creating a formula to copy text from one column to another

[giuredan]

Hello.
I have used this formula that i had found online:
=RIGHT(B115,LEN(B115)-SEARCH("_",B115,SEARCH("_",B115,SEARCH("_",B115,1)+1)+1))

Now i need this formula to do the following:

Scenario A:

Column B: has the following value: APP_AEPEA_705-01-00_4.7.4
Column C: i need it to have the value of: 4.7.4

As well: if column B has the value: APP_80105-00-01_2.1.1 ...... so just 2 underscores within it, i need column C to present the value 2.1.1

So basically after whichever the last "_" is within the string of alphanumeric values anything following after the last "_" i need it to be transported over to column C no matter whether it is numeric or alphabetical.

The formula i have provided above, has helped me with alphanumeric strings that contained 3 "_" units but not with 2 "_" or "abcdefg" values.

If someone know's how to create this formula to work on all that would be greatly appreciated.

Thanks a bunch.

Dan

[royUK]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[Andrew-R]

Right, are you ready for this?

Assuming your value is in A2 the simple little formula you need is ...

=IF(ISERR(FIND("_",A2,FIND("_",A2,FIND("_",A2)+1)+1)),RIGHT(A2,LEN(A2)-FIND("_",A2,FIND("_",A2)+1)),RIGHT(A2,LEN(A2)-FIND("_",A2,FIND("_",A2,FIND("_",A2)+1)+1)))

Lovely, eh?

[giuredan]

wow LOL
what a beauty she is.
now if i wanted to take that formula and reverse it, in the sense of :

AENV_APP_00248651-00-00_BOAT <----- this is the string

and i want all the text to be copied from before the word BOAT (excluding the last underscore) and THEN combine then text that i have in column C which i used your formula for, obviously with the small change of having all the A's changed to C's for column search.....how the heck would i do that?

=IF(ISERR(FIND("_",C2,FIND("_",C2,FIND("_",C2)+1)+1)),RIGHT(C2,LEN(C2)-FIND("_",C2,FIND("_",C2)+1)),RIGHT(C2,LEN(C2)-FIND("_",C2,FIND("_",C2,FIND("_",C2)+1)+1)))


that's what i used :D with your help

[Conrad Green]

I would use :

=RIGHT(B1,LEN(B1)-IF(ISERR(FIND("_",B1,FIND("_",B1,FIND("_",B1)+1)+1)),FIND("_",B1,FIND("_",B1)+1),FIND("_",B1,FIND("_",B1,FIND("_",B1)+1)

to get the last bit &

=LEFT(B1,IF(ISERR(FIND("_",B1,FIND("_",B1,FIND("_",B1)+1)+1)),FIND("_",B1,FIND("_",B1)+1),FIND("_",B1,FIND("_",B1,FIND("_",B1)+1)+1))-1)

to get the first bit