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.
 

imightbewrong

Distinguished Member
How can there be no delimeter? How do you know where the forename ends and the surname begins?

Example cell?
 

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.
 

imightbewrong

Distinguished Member
Yep you can use any delimeter you like (would have answered earlier but had to get a cuppa :))
 

imightbewrong

Distinguished Member

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

Podcast: Large Screen HDR TV or Projector For Home Cinema + Best of the Month
Support AVForums with Patreon

Top Bottom