Getting Certain Text from cell

[volsfan210]

Hi -

Have a group of cells that have text in them but I only wont certain text from the cells.

I only need to pull the "Test 1, Test 2, etc" out of each cell.

The problem is the "Test 1" isnt the same number from the front on each one so a Trim or Mid hasnt worked for me.

Any ideas!

Thanks as always!!

 

[ExcelToDAX]

Assuming your data is in A1:A8... how about:

=LET(vals,A1:A8,TEXTAFTER(vals," ",LEN(vals)-LEN(SUBSTITUTE(vals," ",""))-1))

 

[FormR]

Hi, just another option you can try:

Book3
	A	B
1	WBS: 000122345.ic Test 1	Test 1
2	WBS: 000122345.ic Apple Test 2	Test 2
3	WBS: 000122345.ic AB Test 3	Test 3
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=TEXTAFTER(A1," ",-2)

 

[Fluff]

Another option

=textafter(a1:a8 " ",-2)

 

[volsfan210]

Thanks All -

That makes it work on the basic ones but when I look through all my data and noticing that its not always just Test 1 etc

I want to be able to pull the 1st word after the abbreviation or number!

Thanks,!

 

[FormR]

Hi, how about also posting the sample data in a way that we can copy it for testing?

I want to be able to pull the 1st word after the abbreviation or number!

That's not clear for me, perhaps you can also include a manually created set of expected results for those examples?

 

[volsfan210]

Hi, how about also posting the sample data in a way that we can copy it for testing?

That's not clear for me, perhaps you can also include a manually created set of expected results for those examples?

Hi -

Uploaded image below!!!

 

[Fluff]

Can you post some sample data, not an image.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[volsfan210]

Can you post some sample data, not an image.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Unfortunately our Admin group has locked down the add in feature so I am not able to do that

 

[FormR]

Hi, this might work for you.

Book4
	A	B
1	WBS: 000123456.ME.ABC Test Excel Data	Test Excel Data
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TEXTAFTER(TRIM(A1)," ",2)