Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
Excel find upper case letters and engine displacements?
I have a few hundred thousand of cell values that look like this:
2001ChevroletBlazerLS Sport Utility 2-Door4.3L 262Cu. In. V6 GAS OHV Naturally Aspirated
I need to convert them into this:
Chevrolet/Blazer/2001/4.3L 262Cu.In. V6 GAS OHV Naturally Aspirated.
The year is always 4 digits and always at the beginning of the the string. All of the engines start with the engine displacement in liters in [number].[number]L. Such as 2.0L or 2.3L or 1.5L.
The first question: How to select for Uppercase letter?
The second question: How do I select for the [number].[number]L.?
My formula would probably end up looking like
=MID(A2,5,[find the second uppercase letter in the string]-1)
&"/"&MID(A2,[find second uppercase letter in the string],[find third uppercase letter in the string])
&"/"&LEFT(A2,4)
&"/"&RIGHT(A2,LEN(A2)-[find engine displacement])
1 Answer
- Anonymous9 years agoFavorite Answer
Assuming there are no space characters or other odd characters before the the 3rd uppercase:
2nd uppercase:
=MATCH(TRUE,INDEX(CODE(MID(A2, ROW(INDIRECT("6:"&LEN(A2))),1))<91, ),0)+5
3rd uppercase
=MATCH(TRUE,INDEX(CODE(MID(A2, ROW(INDIRECT(B2+1&":"&LEN(A2))), 1))<91,),0)+B2
The B2 in the second formula is referring to the first formula. I suggest you use helper columns. Use column B for the first formula, and column C for the second (or if you want, you can substitute the first formula into each of the B2's in the second formula, but it'd be a monster).
So the end formula in D2 will look like:
=MID(A2,5,B2-5)&"/"&MID(A2,B2, C2-B2)&"/"&LEFT(A2, 4)&"/"&RIGHT(A2, LEN(A2)-FIND(".",A2)+2)