Left and Right commands for getting numbers before and after specific special characters

[Shakeel Ahmad]

Hi,

Please see the attached sheet. I have a long list of values in Column "A" where two different values are entered in same cell separated by "------".

I want to get the value before "------" and after "------" in two separate cells.

I've successfully got the left side value in "B" column with "=LEFT(A2,FIND("-",A2,1)-1)" formula but don't know how to get the value after "-------" in "C" column.

Please help.

Regards,

Shakeel

[AlKey]

Enter formula in B2 and drag formula cross to C2 and down

Please Login or Register  to view this content.

v	A	B	C
1	K-----M	K	M
2	9-----13	9	13
3	91-----13	91	13
4	9-------3	9	3

[Aardigspook]

There's probably a better way to do it, but this will do what you want:

Please Login or Register  to view this content.

[Pete_UK]

You can put this in C2:

=SUBSTITUTE(RIGHT(A2,LEN(A2)-FIND("-----",A2)-4),"-","")

then copy down. If you want the result as a proper number, then you can add +0 or *1 at the end (and to the end of your existing formula).

Hope this helps.

Pete

[Shakeel Ahmad]

Thanks AlKey! Your formula worked for me.
Thanks Aardigspook! Your formula also worked for me.

Thanks Pete_UK! But in the list number of "------" are not same in all cells. The person who made the list did not take care of this.

Thanks to all of you!

Regards,

[cbatrody]

OR,

Try the following in C2:

=--RIGHT(SUBSTITUTE(A2,"-",""),LEN(SUBSTITUTE(A2,"-",""))-LEN(B2))

[Shakeel Ahmad]

OR,

Try the following in C2:

=--RIGHT(SUBSTITUTE(A2,"-",""),LEN(SUBSTITUTE(A2,"-",""))-LEN(B2))

This is the easiest method that I can understand easily and it solved my problem. The other formulas worked too but are difficult to understand for a common man like me.

Thank you!

[samba_ravi]

or try below
=TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",15)),15))
or
=ABS(LOOKUP(99^9,RIGHT(A2,ROW(A$1:A$15))+0))

[Aardigspook]

Glad we could help and thanks for the rep.

If that has solved your problem, please take a moment to mark the thread as Solved so others know there's an answer here (see instructions in my signature). Thank you.