Return value from text line

[jstevens]

Is it possible to return a value from a text line using a "Find or Search" function? Text to column does not work as the overall length changes with each record and the only delimiter is a blank space.

Example:
02/27 AMAZON MKTPLACE PMTS AMAZON MKTPLA WA 123.45
02/24 Amazon.com AMZN.COM/BILL WA 9.87

[HansV]

What would you like to return?

[jstevens]

Hans,

The value in each record. In the two examples: 123.45 and 9.87

[HansV]

Let's say the first text is in A1.
Enter the following formula in B1, then fill down.

=--TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),255))

[jstevens]

Thanks Hans!

[snb]

A UDF:

Code: Select all

Function F_snb(c00)
  F_snb=filter(split(c00),".")(0)
End Function

in cell C1: =F_snb(A1)

[LisaGreen]

@snb...

How would that work for both a dot and comma decimal delimiter if you were unsure of where the data was coming from... And then there's a dot for date delimiter as well in for example Germany as was pointed out by Alan in another thread???

Lisa

[snb]

If you are unsure you'd better keep far from VBA.....

[LisaGreen]

With respect... I think you've misinterpreted my post snb.

The point of it was that depending on a dot being in the string in the right place is precarious at best and wrong at worst.

The number at the end may not even contain a decimal delimiter at all but may be an integer such as 1 or 2 or even 177743.

Lisa

[HansV]

Lisa, here is a version that works in the situations you mention:

Code: Select all

Function F_snb(c00)
  Dim a() As String
  a = Split(c00)
  F_snb = Val(a(UBound(a)))
End Function

[LisaGreen]

That's great... thank you Hans!

An improvement indeed!

I could be wrong... but I think though, ISO rules for numbers, include a space for "thousands" .. not sure about that.. going to look that up now.

Lisa

[HansV]

Numbers with spaces will be more difficult to handle.

[LisaGreen]

It appears that I was wrong. The international standard for number formats does not appear to be standard at all.

From a *brief* search.. it seems that a comma OR a dot can be used as a decimal delimiter.
Also... America uses a comma as a thousands delimiter while some others use a dot.

An example is ...
Slovak Euro 12 345 678,00 €

Using an atypical for continental Europe, dot for the decimal and a "thin space" whatever that is.. for thousands delimiter.

It would be nice if everyone made some decision about this but I feel that's a long way off!!!!

I wonder what a truly international function would look like to return a "standard" number format. I think it may be quite big. Perhaps a REGEX expression would help.

Lisa

[HansV]

From ISO 31-0:

Numbers consisting of long sequences of digits can be made more readable by separating them into groups, preferably groups of three, separated by a small space. For this reason, ISO 31-0 specifies that such groups of digits should never be separated by a comma or point, as these are reserved for use as the decimal sign.

To expand snb's function to take all kinds of formatted numbers into account would be a lot of work; I don't think we need to do that in this thread.

[LisaGreen]

Agree... thank you Hans.