Breaking out info in an Excel File 1

[LouieGrandie]

Have a CVS file that when I open it in Excel one cell for address looks like this:


Richard D. Hayward
925 3rd Street West

I need to break each line out into three separate columns for address line 1, line 2 and line 3.

While =LEFT(A1,FIND(CHAR(10),A1)-1) works just fine to break out the first line into:
12 Security Forces Squadron/S4R

how do I break out line 2 and line three?

Using:
=SUBSTITUTE(A1,CHAR(10)&CHAR(10),"|")

results in:
12 Security Forces Squadron/S4RRichard D. Hayward925 3rd Street West.

 

[SkipVought]

hi,

You probably have a LINE FEED character in your string.

Paste this code into a MODULE in your VB Editor (alt+F11 toggles between the sheet & editor)

Function ParseIt(str As String, position As Integer)
'str is the string to parse
'position is the line position of the part to extract

    Dim a, i As Integer
    
    a = Split(str, vbLf)
    
    For i = 0 To UBound(a)
        If i + 1 = position Then
            ParseIt = a(i)
            Exit For
        End If
    Next
End Function

Use on your sheet as you would any spreadsheet function...
[

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LouieGrandie]

Cannot tell you how much I appreciate this.