Formula to return value if cell contains a character within the first 10 characters

[DataBlake]

I am stumped. i don't know why i can't figure this out.
Or maybe theres a more efficient way to do this so i'll be super descriptive.

So in column C:
#1 is how it is supposed to look, but it may also have a P or LT in front of it.
#2 & #3 are examples of P and LT, but they are also supposed to have a "/" where the " " is
So what i need in column D is the value as shown for #1 .
#2 should be "LT245/75R16" and #3 should be "P245/50R20"

so if there is an easier way let me know, but what i was thinking is a formula that check the first 5 letters for "P" and "LT" and if it is LT return the first 11 characters, P returns the first 10 characters, and neither returns the first 9 characters.

=IF(FIND("LT",LEFT(C804,5),0),LEFT(C804,11),"") was my thought process but it returns #Value

 

[jtakw]

Hi,

Here's one way:

Book1
	C	D
1	245/35ZR21 XL AZENIS FK510	245/35ZR21
2	LT245 75R16 Pirelli Scorpionn ATR E 120 116R RB	LT245/75R16
3	P245 50R20 Pirelli Scorpion STR 102H RB	P245/50R20
Sheet288

Cell Formulas
Range	Formula
D1	=SUBSTITUTE(LEFT(C1,FIND(" ",SUBSTITUTE(SUBSTITUTE(C1," ","/"),"/"," ",2))-1)," ","/")

Formula copied down.

 

[DataBlake]

Thank you SO much. i didn't know substitute was a thing.

 

[Rick Rothstein]

...and neither returns the first 9 characters.

This is apparently not correct... the first shown example line (Row 791) does not start with either a P or an LT and it has 10 characters. It appears that some of these non-P, non-LT codes are either 9 or 10 characters. Can you please clarify this for us?

Also, are the codes always followed by a space character? If so, this formula would work...

=SUBSTITUTE(LEFT(C1,FIND(" ",C1,9)-1)," ","/")

 

[jtakw]

Thank you SO much. i didn't know substitute was a thing.

You're welcome.