VBA to break text up in single cell

[talmrubin]

I have the following cells of data. Each cell has data that I needed broken out into single cells. However, my customer can have the following variations: CUSTOMER, CUSTOMER ONE, CUSTOMER TWO. I would normally use Text-to-Columns, however since my customer could have a space, it messes up some cells.

CUSTOMER 1369749 HARVEYT $0 ODate 7/7/2017
CUSTOMER ONE 1369592 BUEHLC $96 ODate 7/6/2017
CUSTOMER ONE TWO 1369592 BUEHLC $96 ODate 7/6/2017

[Leith Ross]

Hello talmrubin,

Welcome to the Forum!

1) How many digits will the number have after the customer's name?

2) Will the amount allows be an even dollar amount like $96 ?

3) Will the date always be formatted as ODate m/d/yyyy ?

[talmrubin]

Leith
Thanks for taking a stab, please see below"

1) How many digits will the number have after the customer's name?
7 digits

2) Will the amount allows be an even dollar amount like $96 ?
No, It could go out to the hundreths, so 96.25

3) Will the date always be formatted as ODate m/d/yyyy ?
It will actually be ODATE: m/d/yyy (I added in the colon)

[CK76]

You can accomplish this with bit of formula & Text to Columns.

1. Assuming data in A2:A4 range. Enter following formula in B2, copy down. This will extract string occurring before first numeric.
=TRIM(LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&56^7))-1))

2. In C2 following formula. Copy down.
=SUBSTITUTE(A2,B2&" ","")

3. Copy B2:C4 range and paste as value, and split Column C using Text to Column.

4. You can then delete Column A, or keep it as is.

[CK76]

For VBA, you can try below (it will replace column A values).

Please Login or Register  to view this content.

See attached demo.

[Leith Ross]

Hello talmrubin,

This macro will validated each of the fields and place each filed in it's own column on the right next to the original cell. The data is assumed to start in Cell "A1". You can change this in the macro if you need to.

Copy the code below and paste it into a new VBA Module in your workbook. You can run the macro by using the ALT+F8 keys to display the Macro Dialog box.

Please Login or Register  to view this content.

[YasserKhalil]

Thanks a lot Mr. Leith for your great efforts
As for your code I think that "HARVEYT" part is missing from results ...

[Leith Ross]

Hello Yasser,

Thanks for catching my mistake. Right you are. I missed adding a grouping in the Regular Expression. Here is the corrected version...

Please Login or Register  to view this content.

[talmrubin]

The above works well. What if I removed the 'ODate'? Can you help me adjust the code above?

CUSTOMER 1369749 HARVEYT $0 7/7/2017
CUSTOMER ONE 1369592 BUEHLC $96 7/6/2017
CUSTOMER ONE TWO 1369592 BUEHLC $96 7/6/2017

[talmrubin]

For VBA, you can try below (it will replace column A values).

Please Login or Register  to view this content.

See attached demo.

The above works well. What if I removed the 'ODate'? Can you help me adjust the code above?

CUSTOMER 1369749 HARVEYT $0 7/7/2017
CUSTOMER ONE 1369592 BUEHLC $96 7/6/2017
CUSTOMER ONE TWO 1369592 BUEHLC $96 7/6/2017

[Leith Ross]

Hello talmrubin,

This will remove the Odate from the output.

Please Login or Register  to view this content.

[jindon]

Try

Please Login or Register  to view this content.

[CK76]

The above works well. What if I removed the 'ODate'? Can you help me adjust the code above?

Here you go. Change "Range("B2").Resize..." to "Range("A2").Resize to replace original strings or keep it as is.

Please Login or Register  to view this content.