Remove Text+Numbers Sometimes, Based on Other Text in Cell

[eugeniusjr]

I have a new problem that I'm not sure how to deal with. I only have MS Excel Starter, so I can't use macros, and I don't understand VBA. If there's a formula solution that would be awesome.

I have the following string of text:

P2(BB) $50 - VP:53 PFR:39 AF:4.0 W:10|33 STL:69|63 3B:10|67 CB:62|87 N:-109.18 Hands:102

Sometimes the term "Hands:..." is listed. Sometimes it is not. When it is not listed then I want the data N:.... to be deleted. When it is listed then I want the data N:.... to show up.

Right now I have the following formula: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A5,FIND("N:",A5)-1),"P1","Villain "),"Me","Hero "),"P1","Villain ") (It mostly changes the names at the beginning of the text to Hero or Villain.

Another way to look at the problem is this: Whenever the string starts with "Me" then I want "N:-109.18" to be deleted. Whenever it starts with anything else I want it to stay. The N:xxxxxxxx can be any length. It will always be preceded and followed by a space. It is always in the same spot relative to the other data in the cell.

Thanks guys and gals!

[martindwilson]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[eugeniusjr]

I have attached a spreadsheet showing what the data looks like in its original forms and what the output should look like.

Thank you!

[Ace_XL]

maybe

=IF(ISNUMBER(SEARCH("Hands",A4)),A4,LEFT(A4,FIND("N:",A4)-1))

[Ron Coderre]

I *think* this is what you want....
Using your posted workbook:

This regular formula replaces
"Me(" with "Hero("
"P1(" with "Villain"
AND
Removes the "N:" phrase if the string begins with "Me("

Please Login or Register  to view this content.

Copy that formula down as far as you need.

Is that something you can work with?