Excel question

Mr_Wistles

Distinguished Member
Morning dudes and duddetes. I have an Excel problem that has got me stumped.

I have a list of cells which contain names (forename and surname) and a code number. I need to split this into three separate cells but I am unsure how to do it as the names are different lengths. Normally I would just use text to columns but the source data does not have a delimiter in it.
 

Mr_Wistles

Distinguished Member
James Smith 12345678910
Abe Christianson 549753120

A dreadful extract but it apparently the best I can be given.
 

Mr_Wistles

Distinguished Member
I have been trying all combinations of LEFT, RIGHT, BLANK etc. for the past 30 mins. In the end all it took was five mouse clicks!
 

FruitBat

Well-known Member
Watch out for any exceptions (e.g. Arthur Conan Doyle). If there are any, you should be able to pick them up and correct manually.
 

Stinja

Distinguished Member
I propose a duel to settle this!

Pivot tables at dawn!

Watch out for any exceptions (e.g. Arthur Conan Doyle). If there are any, you should be able to pick them up and correct manually.
You could knock up some VBA, and assume any multi names will be surnames to cut down the number of instances, by say putting in a hypen, then run space delimiter, then take out the hypens?
 
Last edited:

imightbewrong

Distinguished Member
Pivot tables at dawn!

Any place any time.


You could knock up some VBA, and assume any multi names will be surnames to cut down the number of instances, by say putting in a hypen, then run space delimiter, then take out the hypens?

Depends what the requirement is - you could easily add a formula column to pull the code into a single column for instance.
 

The latest video from AVForums

Panasonic LZ2000, LZ1500 & LZ980 Hands-on Launch Event | No QD-OLED for 2022, new 77-inch for LZ2000
Subscribe to our YouTube channel

Full fat HDMI teeshirts

Support AVForums with Patreon

Top Bottom