Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Address Recognition and Separation
Hello,
I have a large ammout of data that I'm attempting to sort by location however the source that I receive my data from sends it to me with the names and addresses all in one colum. I have been able to seperate, not effectivly, the names from the addresses using text to column but I need to be able to pull the city, state and zip into other columns without having to manually edit each of my 6,000 per month entries. Is there any thing I can use that will recognise the address and seperate it out... even if it is outside of Excel? Thank you, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Address Recognition and Separation
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Address Recognition and Separation
Post sample of your data to give us some guidance.
-- Gary''s Student - gsnu200771 "THE ANALYST" wrote: Hello, I have a large ammout of data that I'm attempting to sort by location however the source that I receive my data from sends it to me with the names and addresses all in one colum. I have been able to seperate, not effectivly, the names from the addresses using text to column but I need to be able to pull the city, state and zip into other columns without having to manually edit each of my 6,000 per month entries. Is there any thing I can use that will recognise the address and seperate it out... even if it is outside of Excel? Thank you, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Address Recognition and Separation
Hi
You might like to use this UDF. Function TEXT2COL(ByVal txt, Optional start As Integer, Optional q As Integer) As String Dim tmp As String, i As Integer, str() As String txt = Application.Substitute(txt, ",", "") str() = Split(txt, " ") If start = 0 Then 'Just remove commas TEXT2COL = txt 'this is OK ElseIf start = 1 And q 1 Then For i = start - 1 To start + q - 2 tmp = tmp & str(i) & " " Next i TEXT2COL = Trim(tmp) Exit Function ElseIf start = 1 And q = 0 Then TEXT2COL = str(start - 1) End If End Function It works like Excels Mid function except in words rather than characters Example Data James Graham, 16 the Low Road Results and formula enter James Graham 16 The Low Road =PROPER(TEXT2COL($A$15)) James Graham =TEXT2COL($A$18,1,2) 16 The Low Road =TEXT2COL($A$18,3,4) James =TEXT2COL($A$18,1) Graham =TEXT2COL($A$18,2) Regards Peter "THE ANALYST" wrote: Hello, I have a large ammout of data that I'm attempting to sort by location however the source that I receive my data from sends it to me with the names and addresses all in one colum. I have been able to seperate, not effectivly, the names from the addresses using text to column but I need to be able to pull the city, state and zip into other columns without having to manually edit each of my 6,000 per month entries. Is there any thing I can use that will recognise the address and seperate it out... even if it is outside of Excel? Thank you, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date number separation | Excel Discussion (Misc queries) | |||
Cell Separation | Excel Worksheet Functions | |||
Letter and numeral separation | Excel Worksheet Functions | |||
Decimals separation "." and "," | Excel Worksheet Functions | |||
Decimals separation by . or , | Excel Discussion (Misc queries) |