Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Help splitting complete address into individual columns

0

Hi there.

I'm a novice in Excel so any help would be appreciated.

A client has supplied the attached Excel sheet and was wondering could we split her complete address in Column C into individual colums (D,E,F etc...) for each line of the address, for the purposes of a variable data project.

There are no delimeters present as far as I can see so I tried 'Text to Columns' to no avail.

Sincerely,

Ronan

(I should mention, this is just a sample size - there will be 6k entries on the final sheet)

excel
posted by: dinevalesco (rep: 2) on Thu Sep 9, 2021 at 7:03 am
Answer
Discuss

Discussion

Hi Ronan and welcone to the Forum

Which version of Excel are you using please? (You didn't state that in your Profile)
John_Ru (rep: 6142) Sep 9, '21 at 12:01 pm
Thanks a million John - that works perfectly.

Much appreciated.
dinevalesco (rep: 2) Sep 10, '21 at 5:59 am
Great! Thanks for selecting my answer Ronan. 
John_Ru (rep: 6142) Sep 10, '21 at 6:07 am
Add to Discussion

Answers

0
Selected Answer

Ronan

Your addresses in column C have both linefeed (ASCII character 10) and carriage return (ASCII character 13) delimiters so, if you have Excel365, you could do this:

  1. in cell D2, paste this (array) formula:
    =SUBSTITUTE(SUBSTITUTE(C2:C12,CHAR(10),"#"),CHAR(13),"#")
    replacing the 12 in bold with whatever your last row number is. That "nested" formula will "spill" over into all this rows, replacing those characters with # (or some other character you're sure isn't in the addresses) and any ## groups (created by the nested formula when the address has all carriage returns).
  2. Select D2:D12 (or whatever) and copy/ paste values only (to fix the single # delimiters)
  3. Use the Text to Columns function (under the Data ribbon), picking the Delimiter as "Other" and # then press Finish.

That will split all the address lines into columns D to H (or more).

Hope this helps.

posted by: John_Ru (rep: 6142) on Thu Sep 9, 2021 at 6:43 pm
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login