Share this content
0
5
13322

How to merge names split between two columns in Excel?

Over the past few years I have created a database of around 23,000 records in an Excel spreadsheet. For reasons which are now no longer applicable, the company surname was put in one column and the initials/first name of the company was put in a separate column - thus Joe W Bloggs & Co Ltd would have Joe W in column A and Bloggs & Co Ltd in column B. I now need to merge the colums into one so that I can import data from an online database vendor which uses only a single column for the full company name. Can anyone help? If I need a macro, can anyone provide one for me for this specific purpose, please? Incidentally, should I ever need to separate the single column into two, is that possible?

Many thanks

Jeremy Kitchin
Jeremy Kitchin

Replies

Please login or register to join the discussion.

avatar
07th Sep 2006 08:47

Oh, by the way...
If you have 23,000+ records you really should be thinking about using Access, rather than Excel, to store the data. You can always use queries to pull the data into Excel if you prefer to use this for crunching, but Access will be a far more useful platform for the core data.

Thanks (0)
avatar
05th Sep 2006 17:05

Try this..
Where A1 and B1 contain the data, use the formula:

=CONCATENATE(A1,B1)

To combine them - then is you want, copy & paste special (values) the new column so that you can delete the original two

Hope that helps

Paul

Thanks (0)
avatar
06th Sep 2006 09:02

I'm with Paul
Hi Jeremy

Paul's solution is the one I would also recommend. If you want to keep the split as an option, as Paul implied simply don't delete the originals but use the new column (?C) for your d/base search.

Because the length and layout of the two fields is in no way consistent I percieve that it would be difficult, if not impossible, to seperate them once combined as value. The =SEARCH function can be used to determine the position No. of any character in a string and in combination with LEFT & RIGHT functions can easily be used to split certain consistent strings, e.g. 'firstname secondname' by looking for the position of the space.

However in your example you have two spaces, so SEARCH would only find the first one (I think, bit rusty!), leaving the middle initial with the surname etc.

Thanks (0)
avatar
06th Sep 2006 09:00

concatenation yes
but you will want a space between names so try

+ col1 & " " & col2

it is not so easy to reverse, because you may have more than two names - so whilst you can search for the position of a space there may be more than one and you won't know which one to split it at!

Thanks (0)
avatar
By mathewr
08th Sep 2006 13:31

separation is possible
If you use Alistair's example, combining the two columns into cell C3 using =C1 & " " & C2, but replace the space with a character (something unobtrusive, say an inverted comma "'"), you will be able to search for that character and separate both elements.

In cell C4, to find the first element: =LEFT(C3,(SEARCH("'",C3)-1))
To find the second element: =MID(C3,(SEARCH("'",C3)+1),12)

The "12" indicated above is the maximum number of characters that should be taken, so increase this accordingly.

I hope that helps too!

Thanks (0)