How to split a value in an Excel cell 1

[Aydan]

Hi, I have a colom where the postal code and cities are entered with a space between them. Now I would like to bring te city over to the colum next to it. Since there are a lot of lines I would like to automate this with a macro. Can anybody help me with this?

 

[RobBroekhuis]

You don't need VBA to do this - worksheet formulas can do the trick. If your column is A, with as an example the format
###### CCCCCCCCCCCCC
where ###### is the postal code and CCCCCC is the city,
then you can put in B1: "=left(A1,6)"
and in C1: "=right(A1,len(A1)-7)"
after you've copied the formulas down, you can copy the entire columns B and C and "paste-special" them as values. Then you can delete column A.
If your postal code is not a fixed length, you'll have to use the FIND() worksheet function to find the position of the space separating the two, e.g.

B1: "=left(A1,find(" ",A1)-1)"

Rob

 

[Aydan]

Brilliant idea Rob! Thank you so very much!

 

[Aydan]

Still a question, everything went well, but now I can not delete the first colum any more since the formulas are reffering to them

 

[Aydan]

Sorry, this was a stupid question, I'm not familiar with excel but still. Found the nice feature "Pase special"
Thanks again for the tip!

 

[bearjam]

Hi , not sure if this will help, but if you have only postal codes and city names , you may try selecting column A,and selecting "DATA" ,"TEXT TO COLUMNS","Delimited" etc.

This splits without formulas.

 

[Aydan]

thanks bearjam, I didn't know about this usefull option in Excel. In my case, this could also help doing the job, my problem was already resolved, but good to know for the next time!!

 

[tony813]

Hello... I was just passing by....
... and yes I'm new...
...however, I thought I would say...thnx to bearjam...
...His posting.. allowed me to learn something new...

Tony_813

 

[origapizni]

Way to go, bearjam! One of my favorite unknown Excel features!

Data/TextToColumns has some other not-at-all-obvious uses as well. For example, if you've ever had a column of numbers that don't add up correctly because some of the cells are text (even though they LOOK like numbers), Data/TextToColumns can fix the whole column in a flash!

Just hilite the column or part of the column, select Data/TextToColumns, accept all the defaults, and Viola. Fixed!

I use it so much that I made a simple one-line macro and added a button to my button-bar.

Sub TextToNumbers()
Selection.TextToColumns
''' ALL Defaults acceptable.
End Sub