Excel 2003 split column by characters?

Question

I have a long data base with addresses I need to split, re-typing is not an option, text to column did not worked for this, I have the whole address in one cell and I want to take the state and zip code out, I am looking for a formula that brings back everything but the last 10, or any other number, characters in the cell, by example:

A1 = 222 calm dr # 101 Fresno, CA 93600

and I just want this = 222 calm dr # 101 Fresno

Please help

Answer 1

if you want to get rid of the last 10 characters,

then copy&paste this in B1

=LEFT(A1,LEN(A1)-10)

then copy&paste down the column.

its pretty obvious where the formula looks at the 10, so if your zip has the extra 4 digits, change it to 14

then you can copy the B column

- right click on B1 or C1

- click paste special...

- click values

and you dont have to worry about the formula any more.

Answer 2

If the state and zip code consistently begin with a comma, you could use the "Text to Columns" feature to split the cell on the comma.  On the file menu, click Data > Text to Columns.

This would place the State and ZIP code in the next column.  I could help you better if I had a copy of the excel file.

Answer 3

I may be misreading your question, but it seems that you want to REPLACE the complete address in the SAME cell as the complete address is now in.

If so, these macros will do just that.  If your addresses are not in Col A, change both A's in 'A1' and 'A65536' to the column letter you are using.  If the first address is not in row 1, change the '1' in A1 to the starting row.

Open your workbook

Copy these to the clipboard

Sub Trim_StZip()

Dim rng As Range

Set rng = Range("A1:" &  _

Range("A65536").End(xlUp).Address(0, 0))

For Each cell In rng

cell.Select

StripIt

Next

ctr = 0

End Sub

Sub StripIt()

Dim i

Dim ctr

For i = 1 To Len(ActiveCell)

If Mid(ActiveCell, i, 1) = "," Then

ActiveCell.Value = Mid(ActiveCell, 1, ctr)

Else

ctr = ctr + 1

End If

Next

End Sub

Press ALT + F11

Insert  >  Module

Paste the macros into the Module space to the right.

Close back to Excel

Go to Tools  >  Macro  > Macros

Highlight the Trim_StZip macro.

Click 'Options'

Select a letter to be used as a keyboard shortcut.

Close back to Excel

Press CTRL + your shortcut letter to run the macro.