How to extract value without spaces from a cell?

English
#1

In column D I have strings composed by a number and “EUR”, but sometimes I have them in USD and EUR, like in D7: ( 1,74 USD) 1,69 EUR . In column E I want only the values, so I can format them as money.

This is what I got so far. To use an example, in E7 I tried with =SUBSTITUTE(IF.ERROR(SUBSTITUTE(D7;MID(LEFT(D7;FIND(")";D7));FIND("(";D7);LEN(D7));"");D7);"EUR";"€"), but I’m getting 1,69 € .

I don’t mind that much about the “€” sign since LO seems to be able to operate with the cell as it is (for some reason, because it’s either a number, so no spaces should be showed, or is a string, so I shouldn’t be able to operate with it), but I don’t understand why if I use TRIM(E7) it doesn’t change at all.

Here’s a test with some random cells:
LO trimmed values.ods (20.9 KB)

#3

I suggest you to revise your data input method. Your data located in the column D are practically unusable in a spreadsheet:

  • somewhere there are bracket , somewhere there are not;

  • somewhere there are one space, and somewhere there are two spaces between the parts of the values.

  • somewhere there are only euro values, somewhere there ere USD+EUR values.

You must always use huge and redundant formulas to them.

1 Like
#9

A couple of side notes.

You should be mindful about data type. Some calculations will take numeric strings as numbers, but other calculations will disregard them.

To illustrate, you could try this:

  • In an empty sheet, type '7 (an apostrophe followed by the digit 7) into cell A1
    The leading apostrophe forces the digit to be interpreted as character data.
  • In cell B1 type +A1
  • In cell C1 type =SUM(A1)

You’d expect the two formulas to return the same number. However, the explicit math operator insists that "“here should be a number” so Calc does a best effort to extract an unambiguous number, returning 7, while SUM() function disregards text entries (even when they represent valid numbers) so it should return a zero. This behavior is by design (not a bug), but if your data are not consistent it can give errors which are difficult to discover.

The “persistent spaces” in your spreadsheet are not plain space characters, but non-breaking spaces (which are not targeted by the TRIM() function).

#2

An option:

=REGEX(REGEX(A2,"[:digit:]+,[:digit:]+[:space:]+EUR.*$"),"^[:digit:]+,[:digit:]+")

image
image976×185 27.5 KB

1 Like
#4

Maybe, for those of us who default to a point for decimal separator, a small modification: =NUMBERVALUE(REGEX(REGEX(A2,"[:digit:]+,[:digit:]+[:space:]+EUR.*$"),"^[:digit:]+,[:digit:]+"),",")

#5

A shorter variant:

=TRIM(REGEX(A2;"\d*,\d+\s*(?=EUR)"))
1 Like
#6

Hi! This actually works, thanks! Although I cannot change it into money format for some reason.

Also, I found a cell with 103,05- EUR (as -103,05 €), is there any way to convert it into that?

Don’t ask me who the hell created the spreadsheet, this person is clearly frustrated with this world.

#7

Regex and trim are Text-functions, the result ist a string. Formatting can’t change the type of the content, but the function NUMBERVALUE can, as already shown in the comment by @EarnestAl above.
.
So convert the result by NUMBERVALUE(), then your “money format” will be shown.

#8 
=NUMBERVALUE(REGEX(REGEX(REGEX(A2;"-?\d*,\d+-?\s*(?=EUR)");"\S*");"(.*)-";"-$1");",")
2 Likes
#12

Hi there! Missed your reply!! It’s amazing, thanks a lot for that!! I’m looking for more info about how to use ?, \d, \s…). Do you know of any place to find more information about it?

#13

Perhaps in the documentation?

1 Like
#14

I didn’t know they were called regular expressions, thanks a lot!

#15

The link is at the bottom of the Help page for the REGEX function - among other places. That is how I got to it.

2 Likes
#10

I post this as a solution despite the fact that it’s more a lesson about “spreadsheeting”.
Much of what’s contained here (in the attachment) was already mentioned in other posts.
Thank you for this extremely educative example!
It shows so many things we should avoid in spreadsheets, and the problems that they can create.
Even when you start to use quite complicated formulas, you don’t get really reliable results without adding advanced AI.
(I have also tried to deal with the complications that arise when one finally wants to apply such a spreadsheet in different “locales” in the edited version of your example that is attached.

Concerning the details you need to study the attachment.

Created with the help of www.DeepL.com/Translator (free version)

disask93497moneyValuesFromConfusingText.ods (32.8 KB)

1 Like
#11

Woah! OMG!! I got the date from copying/pasting from the numbers showed on my bank account! I will definitely study all of this! Thanks a lot!

#16

Although, seems like VALUE can deal with the spaces around the number.
=VALUE(REGEX(E3;"-?\d*,\d+\s*(?=EUR)"))
joining the spaces with EUR, returns the number only
=VALUE(REGEX(E3;"-?\d*,\d+(?=\s*EUR)"))

Maybe NUMBERVALUE could be safer to deal with decimal separator.
=NUMBERVALUE(REGEX(E3;"-?\d*,\d+(?=\s*EUR)") ;",")
but seems it doesn’t treat those spaces like VALUE does, so needs a clean number.

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.