VBA text to columns for a document of inconsistent strings

Obzen3

New Member
Joined
Aug 10, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
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 2Q12:toName:fromName:1112.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!
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,336
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
I don't know if you can with text to columns but you can do it easy enough with formula...

Book1
ABC
2123456 *toName 4 100.00 234567 fromName 2 12.000 2 Q12:toName:fromName:1 165.0 183.7 26.0 185.5 112.5123456 *toName 4 100.00 234567 fromName 2 12.000 2Q12:toName:fromName:1 165.0 183.7 26.0 185.5 112.5
3234567 *toName2 2 17.000 345678 fromName 2 13.000 1 Q63:toName:fromName:1 120.0 125.6 2.0 125.6 104.1234567 *toName2 2 17.000 345678 fromName 2 13.000 1Q63:toName:fromName:1 120.0 125.6 2.0 125.6 104.1
4345678 *toName! 8.000 fromName2 WND 3 1 Q12:toName:fromName:1 5.3 17.7 8.8 19.8 373.4345678 *toName! 8.000 fromName2 WND 3 1Q12:toName:fromName:1 5.3 17.7 8.8 19.8 373.4
Sheet1
Cell Formulas
RangeFormula
B2:B4B2=LEFT(A2,SEARCH("Q??:toName:fromName",A2)-1)
C2:C4C2=RIGHT(A2,LEN(A2)-LEN(B2))
 

Obzen3

New Member
Joined
Aug 10, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,109,397
Messages
5,528,495
Members
409,820
Latest member
gabrielrms

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top