Extract Data from Column to 1 Rows for Importing

Perhaps this could be of help to you?

To test it run macro "SplitData". As I'm not familiar with the US Zip code I've not split city and zip. Should "Bolton, MS 39041-8802" be split as
"Bolton" and "MS 39041-8802" or should it be "Bolton" and "39041-8802"?

Alf

Thank you so much for your help.

Yes, each should be split into separate columns as Bolton is the city, MS is the state, and 39041-8802 is the zip code.

I am new to this forum, how come I cannot see your attachment or the attachment I provided? Where would it be located?

It was there (i.e. yours and mine) but now it's gone. I guess it's the parts of the hiccups the excel forum goes through after the data base upgrade. I have down loaded your file and done my macro testing on it. What's now is missing is the last split "Bolton" "MS" and "39041-8802".

It seems to me that you have not had a chance to test the macro I wrote since the file I uploaded seems to be gone. I'll post the macro code and upload the file so you can have a chance to test it yourself to if this is what you are looking fore.

Normally attachments should be found at the end of one's post.

Option Explicit

Sub SplitData()
Dim lastrow As Long
Dim i As Integer
Dim k As Integer

Application.ScreenUpdating = False

k = 4

lastrow = Range("C" & Rows.Count).End(xlUp).Row

For i = 5 To lastrow

If Cells(i, 2).Value <> "" Then
    Range(Cells(k, 3), Cells(i - 1, 3)).Copy
        Cells(k, 10).PasteSpecial Paste:=xlPasteValues, Transpose:=True
    k = i
End If

Next i

If i = lastrow + 1 Then
    Range(Cells(k, 3), Cells(i - 1, 3)).Copy
    Cells(k, 10).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End If

Columns("J:N").Columns.AutoFit

Application.CutCopyMode = False

Application.ScreenUpdating = True

End Sub

Alf

The test worked just as you indicated.
Thank you so much for your help. That is exactly what I need to do.
Is it possible to further separate the "City" - Bolton, "State"- MS, and "Zip Code"- 39041-8802 ?

Is it possible to further separate the "City" - Bolton, "State"- MS, and "Zip Code"- 39041-8802 ?

It certainly is and if I can't help you there are a number of clever people in this forum who certainly can do it!

I thought I had it solved but found not only "State" - MS but also PA and LA. Are there only a small number of possible states in your data or could any of the 52 be found in it?

The "Zip Code" is it always in the form of 5 numbers + a hyphen + 4 numbers?

Still you problem is quite interesting and I do like a challenge so I'm still working on it.

Alf

Ok, here we go. I do think I've sorted out all the bugs and it splits the values the way you wanted but you better test it.

As usual you run the macro "SplitData". Hopefully this workbook does not disappear.

As I think this solves your problem could you please mark your thread "Solved" and if you would like to rate my answer click on the small star in the lower left corner of my last post.

Just to be sure in case of the file disappearing I'll add the macro code as well.

Sub SplitData()
Dim lastrow As Long
Dim i As Integer
Dim k As Integer

Application.ScreenUpdating = False

k = 4

lastrow = Range("C" & Rows.Count).End(xlUp).Row

For i = 5 To lastrow

If Cells(i, 2).Value <> "" Then
    Range(Cells(k, 3), Cells(i - 1, 3)).Copy
        Cells(k, 10).PasteSpecial Paste:=xlPasteValues, Transpose:=True
        Cells(k, 9 + i - k).TextToColumns Destination:=Cells(k, 9 + i - k), DataType:=xlDelimited, Comma:=True
    Cells(k, 9 + 1 + i - k).TextToColumns Destination:=Cells(k, 9 + 1 + i - k), DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 1), Array(3, 1)), TrailingMinusNumbers:=True
     
    k = i
    
End If

Next i

If i = lastrow + 1 Then
    Range(Cells(k, 3), Cells(i - 1, 3)).Copy
    Cells(k, 10).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
     Cells(k, 9 + i - k).TextToColumns Destination:=Cells(k, 9 + i - k), DataType:=xlDelimited, Comma:=True
    Cells(k, 9 + 1 + i - k).TextToColumns Destination:=Cells(k, 9 + 1 + i - k), DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 1), Array(3, 1)), TrailingMinusNumbers:=True
    
End If

Columns("J:O").Columns.AutoFit

Application.CutCopyMode = False

Application.ScreenUpdating = True

End Sub

Alf

Alf,

I cannot tell you how much I appreciate your help. This saved me a large amount of time trying to figure this out myself when it is something I do not do. I used macros in excel some 20 years ago but it is all different now.

Again, thanks,
Bubba

Hi Bubba,

Glad you liked it and found it helpful. Myself I gained a bit more Excel knowledge so to sum it up a typical win / win situation.

Regards

Alf

Ps Don't forget to mark your thread "Solved"

I cannot figure out how? Could you again help me?

Sure, no problem

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

Hope this helps

Alf