Finding 5th occurrence of a char in a string

[David Morrison]

I have a cell containing this string:

1500 | 1500 | 1500 | 2500

I want to extract the fourth number. The two ways I can see of doing
this would be to search for the third | then using the RIGHT() function,
get my number. Alternatively, I could search backwards from the end to
find the |, then extract my number.

Unfortunately, I cannot see any way of doing either of these in Excel
2004.

There must be a way somehow.!

I would be grateful for an example or a pointer to where it is explained.

 

[JE McGimpsey]

I have a cell containing this string:

1500 | 1500 | 1500 | 2500

I want to extract the fourth number. The two ways I can see of doing
this would be to search for the third | then using the RIGHT() function,
get my number. Alternatively, I could search backwards from the end to
find the |, then extract my number.

Unfortunately, I cannot see any way of doing either of these in Excel
2004.

There must be a way somehow.!

One way:

=MID(A1,FIND("$",SUBSTITUTE(A1,"|","$",LEN(A1) -
LEN(SUBSTITUTE(A1,"|",""))))+1,255)

 

[CyberTaz]

One way:

=MID(A1,FIND("$",SUBSTITUTE(A1,"|","$",LEN(A1) -
LEN(SUBSTITUTE(A1,"|",""))))+1,255)

Hi John -

Help me here Am I missing some kind of hidden message in the post? Based
on what was stated I find that using =RIGHT(cellref,4) works just fine. The
result of 2500 [based on the OP's example] is returned left aligned but is
still rendered as a value - it's just a matter of reformatting the cell.

What am I not seeing in the query & why would there be no "way of doing
either" - in layman's terms if you please

Regards |::>)
Bob J.

 

[JE McGimpsey]

Help me here Am I missing some kind of hidden message in the post? Based
on what was stated I find that using =RIGHT(cellref,4) works just fine. The
result of 2500 [based on the OP's example] is returned left aligned but is
still rendered as a value - it's just a matter of reformatting the cell.

What am I not seeing in the query & why would there be no "way of doing
either" - in layman's terms if you please

Well - if *every* last number was exactly 4 digits, RIGHT(A1,4) would
certainly work. I didn't assume that was the case.

If that last number is 1,2,3,5, etc. digits, my formula still works.

RIGHT(), like MID(), though, will always return Text, which is why the
displayed value is left aligned (try

=ISTEXT(B1)

to verify). However, the strings returned by the formulas behave
differently than text literals as arguments for math operators - so you
can use them in other formulae, e.g.:

=B1 + 2

 

[CyberTaz]

Thanks John -

Well - if *every* last number was exactly 4 digits, RIGHT(A1,4) would
certainly work. I didn't assume that was the case.

I was interpreting the data as being consistently 4 digits, but what
perplexed me was the statement:

Perhaps that's what the OP meant - it didn't work because the data wasn't
consistently the last 4 digits.

If that last number is 1,2,3,5, etc. digits, my formula still works.

No question - definitely the right solution if the number of characters is
variable. I learned long ago to respect your equations

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[David Morrison]

One way:

=MID(A1,FIND("$",SUBSTITUTE(A1,"|","$",LEN(A1) -
LEN(SUBSTITUTE(A1,"|",""))))+1,255)

Hi John -

Help me here Am I missing some kind of hidden message in the post? Based
on what was stated I find that using =RIGHT(cellref,4) works just fine. The
result of 2500 [based on the OP's example] is returned left aligned but is
still rendered as a value - it's just a matter of reformatting the cell.

What am I not seeing in the query & why would there be no "way of doing
either" - in layman's terms if you please

2500 is just a sample value. It may be anything from 0 to several
million, ie, it will not always be 4 digits.