Truncate cell ONLY if it contains string?

Hi all!

So Column W has emails, which occasional have long generated ones like so "guest-XXXXXXXXX"
Other cells have normal emails.

I want to truncate to say 10 characters (or whatever) only the cells with "guest-"

Is this possible? Also must say this column is generated with a formula so that is important to, this is the existing formula in the cell which parses email from another column.

Please Login or Register  to view this content.

So basically, if the data in the cell from the formula above contains "guest-" I want to truncate to 10 characters.


(Can you even truncate results into the same cell as the data? Or would it have to go in a new column?)

I think I was able to do it awhile back but I can't recall how I did it =D

My form is huge, has VLOOKUPS/INDEX and buncha stuff but I stripped it over only the two columns in question.
I may just add a section column and hide the other.

Edit:

Also the formula didn't work all the time, I should have specified the number of digits after "guest-" varies...could be 10, 15, 20 or whatever..auto generated. So the formula didn't work all the time only part of the time.

What do you want to end up with

=Left(your_B2_formula,10) will truncate to 10 characters but I suspect that may not be what you expect.

Try this
Enter formula in B1 and copy down

Formula:

Please Login or Register  to view this content.

	A	B
1	<a href=mailto:[email protected]>[email protected]</a>	[email protected]
2	<a href=mailto:guest-XXXXXXXXX[email protected]>[email protected]</a>	[email protected]

Sheet: Sheet1

Intermediate between the simplicity of Alkey's and the complexity of dominics... my take:

=IF(LEFT(MID(A1,SEARCH(":",A1)+1,255),5)="guest",LEFT(MID(A1,SEARCH(":",A1)+1,255),10),LEFT(MID(A1,SEARCH(":",A1)+1,255),SEARCH(">",MID(A1,SEARCH(":",A1)+1,255))-1))

You'rewelcome and thanks for the rep.