VBA text to columns for a document of inconsistent strings

[Obzen3]

I need a macro to help with text to columns for a document of inconsistent strings.

Ex strings

123456 *toName 4 100.00 234567 fromName 2 12.000 2 Q12:toName:fromName:1 165.0 183.7 26.0 185.5 112.5

234567 *toName2 2 17.000 345678 fromName 2 13.000 1 Q63:toName:fromName:1 120.0 125.6 2.0 125.6 104.1

345678 *toName! 8.000 fromName2 WND 3 1 Q12:toName:fromName:1 5.3 17.7 8.8 19.8 373.4

I want to separate the string at an identifier that starts with the letter Q and some number :
Ex identifier Q12:toName:fromName:1

with everything before the identifier in 1 cell, the identifier in the 2nd cell, and the last number is the 3rd cell.

So that

123456 *toName 4 100.00 234567 fromName 2 12.000 2 Q12:toName:fromName:1 165.0 183.7 26.0 185.5 112.5

Would become

123456 *toName 4 100.00 234567 fromName 2 12.000 2

Q12:toName:fromName:1

112.5

I tried doing this with a fixed width text to columns, but some of the strings have different amounts of spaces and characters in the first portion of the string.

I also tried separating the string at a P character, but some of the names start with P which caused problems.

I am currently using Excel 2013 on Windows 10. Any help or ideas would be greatly appreciated!

 

[MARK858]

I don't know if you can with text to columns but you can do it easy enough with formula...

Book1
	A	B	C
2	123456 *toName 4 100.00 234567 fromName 2 12.000 2 Q12:toName:fromName:1 165.0 183.7 26.0 185.5 112.5	123456 *toName 4 100.00 234567 fromName 2 12.000 2	Q12:toName:fromName:1 165.0 183.7 26.0 185.5 112.5
3	234567 *toName2 2 17.000 345678 fromName 2 13.000 1 Q63:toName:fromName:1 120.0 125.6 2.0 125.6 104.1	234567 *toName2 2 17.000 345678 fromName 2 13.000 1	Q63:toName:fromName:1 120.0 125.6 2.0 125.6 104.1
4	345678 *toName! 8.000 fromName2 WND 3 1 Q12:toName:fromName:1 5.3 17.7 8.8 19.8 373.4	345678 *toName! 8.000 fromName2 WND 3 1	Q12:toName:fromName:1 5.3 17.7 8.8 19.8 373.4
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=LEFT(A2,SEARCH("Q??:toName:fromName",A2)-1)
C2:C4	C2	=RIGHT(A2,LEN(A2)-LEN(B2))

 

[Obzen3]

I had previously tested a solution similar to this but wasn't aware of the wild card '??' placeholders.

I also wasn't previously aware that I could call the excel Search function using Application.WorksheetFunction.Search.

This is definitely helpful for my project.

Thanks for your reply!


In case anyone is curious in the future, this is the line I was testing.

ws.Range("B1") = Left(ws.Range("A1"), (Application.WorksheetFunction.Search("Q??:", ws.Range("A1")) - 1))

ws.Range("C1") = Right(ws.Range("A1"), (Application.WorksheetFunction.Search("Q??:", ws.Range("A1")) + 10))