Excel function question 2

[des0929]

What function would I use to take a string and parse it the following way:

Original string: 20BPP0355
1. Need to find the "BPP" and bring every value to the
right of "BPP" into a new field:

New string: 0355

 

[Bowers74]

Assuming that your string "20BPP0355" is located in cell A1:

=MID(A1,SEARCH("BPP",A1)+3,LEN(A1))

Should work for you!



Peace!!

Mike

Didn't get the answers that you wanted? Then . . . Click Here

 

[KenWright]

With Data in Col A starting A1, in B1 and copied down:-

=RIGHT(A1,LEN(A1)-(LEN("BPP")+(FIND("BPP",A1)-1)))

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[AnotherHiggins]

This should do it:

=RIGHT(A1,FIND("BPP",A1)+1)

 

[KenWright]

Hi ah - That will only work if every string is of the same format and length as the one in the example, but if that were the case then the OP may as well use Data / Text To Columns / Fixed Width.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Bowers74]

Sorry Higgins,

but what if the string is 20[COLOR=red yellow]5[/color]BPP0355? Then yours won't work.

;-)



Peace!!

Mike

Didn't get the answers that you wanted? Then . . . Click Here

 

[Bowers74]

Here's another way:

=REPLACE(A1,1,SEARCH("BPP",A1)+2,"")

;-)



Peace!!

Mike

Didn't get the answers that you wanted? Then . . . Click Here

 

[Bowers74]

Oops,

=REPLACE(A1,1,SEARCH("BPP",A1)[COLOR=green yellow]+LEN("BPP")-1[/color],"")

and my other should have read:

=MID(A1,SEARCH("BPP",A1)+[COLOR=green yellow]LEN("BPP")[/color],LEN(A1))





Peace!!

Mike

Didn't get the answers that you wanted? Then . . . Click Here

 

[bluedragon2]

My 2 cents

=RIGHT(A1,LEN(A1)-(FIND("B",A1)+2))

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[AnotherHiggins]

Not only was I the 3rd to press "Submit," but my answer wasn't even right!

What can I say, it's been a along day and my brain hurts - I clearly under-thought this one.

John

 

[KenWright]

LOL - The times I've wished I could take back pressing that damned Submit button!!!

Hi Blue - Same caveat as with ah's - What if all the data is not of that exact same format and contains other Bs?

eg 2B5BPP0355

Regards
Ken...................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[bluedragon2]

Always true as usual Ken, I just kept the KISS moment in case it was that simple...



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[KenWright]

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Bowers74]

Another way to do this, would be to use Excel's Replace Tool (i.e. Edit->Replace... [or Ctrl+H])

In the "Find What" field enter "*BPP"
' The asterisk " * " before "BPP" tells Excel to replace everything BEFORE "BPP".

In the "Replace With" field Blank or an apostrophy " ' "
' Using the example string, this would return 0355, which Excel will automatically recognize as a number and display just 355. If you want the "0355" to be shown, then enter an apostrophe " ' " in the "Replace With" field.

Just another option. ;-)

Peace!!

Mike

Didn't get the answers that you wanted? Then . . . Click Here

 

[KenWright]

<g> You could also..........

Click on every cell, hit F2, hit left arrow repeatedly until you get to the B before the PP, then, right arrow back to the last P, then backspace back to the start, type apostrophe and then hit enter. then move to the next cell and repeat

It's possible I'd go with one of the other responses before resorting to this one though

Regards
Ken...................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Bowers74]

LOL

Many Roads Lead to Rome (or something) ;-)



Peace!!

Mike

Didn't get the answers that you wanted? Then . . . Click Here