CODEWITHSUNDEEP
google-sheetssplitgoogle-sheets-formulanumber-formatting
Lumo87
Lumo87

Reputation: 23

Need to get number from text string + add decimals

I've been getting really great info here, however can't find a way to pull this one out:

I need to pull from the cell bellow in a way I end up with: "-5000,00" 

12 août **It Brothers LLC** , -5.00000

To note that the text in bold may differ in number of words from line to line.

Upvotes: 1

Views: 89

Answers (2)

ziganotschka
ziganotschka

Reputation: 26796

Provided that your cell content will have one . and one , the following formula will transform

12 août **It Brothers LLC** , -5.00000 to -5000:

=INT(INDEX(SPLIT(substitute(A2,".",""), ","),,2))/100

Now you have two options to transform the number to -5000,00

  1. Add ",00" as a string, e.g. with =CONCAT(A4,",00") However, in this case the output won't be a number.

2.Assign the custom number format to the result cell/column as shown in the images below.

Be aware that either the number will be shown as -5000,00 or -5000.00 depends on your country Spreadsheet Settings.

enter image description here

enter image description here

enter image description here

Upvotes: 1

player0
player0

Reputation: 1

try:

=TEXT(INDEX(SPLIT(A2; ",");;2)*1000; "#0,00")

0

or:

=TEXT(REGEXEXTRACT(A2; ", (-?\d+)")*1000; "#0,00")

0

Upvotes: 0

Related Questions