Select Text in After Using Text to Columns

[Dmichaud61]

Greetings! Is there a formula to fill down in column E with text from the first cell to the left that has text? The text in columns A through D are different lengths and could contain both text and numbers. I've a spreadsheet with more than 1900 rows. Thank you in advance.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AAA[/TD]
[/TR]
[TR]
[TD]AAAB[/TD]
[TD]ATX[/TD]
[TD][/TD]
[TD][/TD]
[TD]ATX[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD]CCCCC[/TD]
[TD]DDDD[/TD]
[TD][/TD]
[TD]DDDD[/TD]
[/TR]
[TR]
[TD]1ADV[/TD]
[TD]CCC[/TD]
[TD]RNO[/TD]
[TD]XYZ1[/TD]
[TD]XYZ1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

If a cell might contain only numbers, this would need modification but I read your comment as if a cell might contain text or text and numbers.

Excel Workbook
	A	B	C	D	E
1	AAA				AAA
2	AAAB	ATX			ATX
3	BBB	CCCCC	DDDD		DDDD
4	1ADV	CCC	RNO	XYZ1	XYZ1
Last column

BTW, this could be achieved without doing the text to columns if we knew what the separator was.

 

[Dmichaud61]

Thank you. Your formula worked on all cells that had only text. But i should have said that some cells contain numbers as well as text.

 

[Dmichaud61]

The separator is always a forward slash "/". For example: AA/B/CCC/DE45/x2se. Thanks

 

[Peter_SSs]

The separator is always a forward slash "/". For example: AA/B/CCC/DE45/x2se. Thanks

In that case try this on the original data before Text to Columns.

=SUBSTITUTE(RIGHT(SUBSTITUTE(A1,"/",REPT("/",50)),50),"/","")

 

[Dmichaud61]

Peter, it worked perfectly. Much appreciated.

 

[Peter_SSs]

Peter, it worked perfectly. Much appreciated.

You are very welcome.

 

[Dmichaud61]

Could the formula be modified to select text between different bracket sets? For example just the text between the first, second, or third set of brackets starting from the left: AA/BBB/CCCCC/22232/44A. First set would be BBB, second would be CCCCC, and third 22232.

 

[Peter_SSs]

Do you mean like this, copied across?

Book1
	A	B	C	D	E	F
1	AA/BBB/CCCCC/22232/44A	AA	BBB	CCCCC	22232	44A
Extract sections

Cell Formulas
Range	Formula
B1	=TRIM(MID(SUBSTITUTE($A1,"/",REPT(" ",50)),COLUMNS($B:B)*50-49,50))

 

[Dmichaud61]

Yes Sir. Thanks!