Formula to find number after 4th dollar sign in a string

[Steve Stad]

Hi,
I have a string of data e.g.,...$0.00 $44.53 $0.00 $32.53. I would like to
write a
formula to find and display the number to the right of (or associated) with
the 4th dollar sign. i.e., in this case 32.53. I know the find formula
will find a charachter - but what if there are more than one of the same
characters in the string e.g., like the dollar sign in the string above.
Thanks
Steve.

 

[T. Valko]

Assuming the value you want is *always* the last group of numbers preceded
by a space character:

=--TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))

 

[Ron Rosenfeld]

Hi,
I have a string of data e.g.,...$0.00 $44.53 $0.00 $32.53. I would like to
write a
formula to find and display the number to the right of (or associated) with
the 4th dollar sign. i.e., in this case 32.53. I know the find formula
will find a charachter - but what if there are more than one of the same
characters in the string e.g., like the dollar sign in the string above.
Thanks
Steve.

If the 4th will always be the last, then you can use this formula:

=--TRIM(RIGHT(SUBSTITUTE(TRIM(A1),"$",REPT(" ",99)),99))

If there can be more than four, then a simple way is to download and install
Longre's free morefunc.xll add-in from http://xcell05.free.fr/morefunc/english/

and use this "regular expression" formula:

=--REGEX.MID(A1,"(?<=\$)\b\d+(\.\d+)?\b",4)

--ron

 

[Bob I]

What determines the number of characters to return after you discover
the "4th" dollar sign?

=FIND("$",A2,(FIND("$",A2,(FIND("$",A2,(FIND("$",A2,1)+1)+1)+1)+1)+1))

 

[Héctor Miguel]

hi, Steve !

another approach... =--mid(a1,search("|",substitute(a1,"$","|",4))+1,9)

hth,
hector.

__ OP __

 

[Steve Stad]

Thank you all for the formula help. The substitute command copies the number
as as number which enables quick calculations. The mid with the four find
functions copies the number as text which does not enable quick calculations
- but does find the right position.

 

[T. Valko]

The mid with the four find functions copies the number

as text which does not enable quick calculations
- but does find the right position.

Easily rectified:

=--FIND(.........)

Or:

=FIND(...........)+0

FIND returns a TEXT value even if it's a number. The "--" or the adding of 0
will coerce the TEXT number to a numeric number which can then be used in
other calculations.

 

[Dave Peterson]

Typo alert:

=mid() returns that text value. =find() returns a number or an error if not
found.

 

[T. Valko]

Good catch.

Thanks, Dave!