Text to Columns: Losing Data?

[msmiley]

Hello -

One of my clients sent me a file of addresses that seems to have been pulled from some sort of database and the formatting on the addresses is a little sloppy. Every line of the address is in one cell and there are some extra characters which excel isn't reading quite right (these characters display as squares at the end of each line -- returns perhaps?)

I am trying to use "text to columns" to separate the different lines of each address into separate columns using these characters (Alt+010 or Alt+0013) as delimiters, but I every time I go through the process and click "finish," information seems to get lost. The first line of the address is in its own cell (yay!), but all subsequent data disappears altogether (boo!).

Anyone have any ideas on why text to columns isn't working for me?

Thanks!
Matt

 

[VoG]

Hello and welcome to MrExcel.

You can try this macro. If it doesn't do anything try substituting Chr(10) with Chr(13) or vbCrLf.

Sub TxttoCol()
Dim LR As Long, i As Long, X As Variant
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        X = Split(.Value, Chr(10))
        .Resize(, UBound(X) + 1).Value = X
    End With
Next i
End Sub

 

[texasalynn]

also wondering if you might have some hidden columns

 

[texasalynn]

here is a macro that I have used before to clean up those unknown characters

Sub TrimChr()
Application.ScreenUpdating = False
Dim cell As Range
Range("A1:A200").Replace What:=Chr(30), Replacement:="-", LookAt:=xlPart
Range("A1:A200").Replace What:=Chr(13), Replacement:="", LookAt:=xlPart
Range("A1:A200").Replace What:=Chr(9), Replacement:="", LookAt:=xlPart
Range("A1:A200").Replace What:=Chr(11), Replacement:="", LookAt:=xlPart
Range("A1:A200").Replace What:=Chr(21), Replacement:="", LookAt:=xlPart
Range("A1:A200").Select
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
'Application.ScreenUpdating = True
End Sub

 

[bp.aga.ca]

The problem is in the "hidden characters", such as the "enter key".

The fix is to use the "clean" function....

STEP 1: Simply use this formula: =clean("cell with hidden character")

STEP 2: Then copy and "past special" (values).

STEP 3: Text to Columns should now work.

Now, there is a chance "clean" does not find your special characters, So you'd have to use "substitute" then "clean".

More information on this here: https://support.office.com/en-us/ar...rom-text-023f3a08-3d56-49e4-bf0c-fe5303222c9d

"Boom" That's me dropping the mic!