Splitting multiple entries in single cell into multiple columns

Good afternoon all,

This is my first post as I am new to the forum

I am looking to split multiple different entries in a single cell into multiple columns and repeat this for all rows

Example (I have the below in a single Cell as column headers)
NCM Server Mgmt VLAN Site ID

Next Line down is the data (Each row in a single cell)
Enabled 10.10.10.0 50 TEST SITE 1
Enabled 10.10.20.0 50 TEST SITE 2
Disabled 10.10.30.0 50 TEST SITE 3


I have very little experience with Excel and VBA as this is not my normal job (Network Engineer) but this would help me out loads if I could acheive this as I have a number of projects where this would become useful

I know you can use delimeters but with spaces between the values I just can't fathom a way forward


Any assistance appreciated


Thanks
David

Highlight the column with the data in (A ?) by clicking on the column identifier. Then do CTRL-H (Find and Replace) and:

Find What: SITE<space>
Replace with: SITE_
Click Replace All

Then CTRL-H again, and:

Find What: TEST<space>
Replace with: TEST_
Click Replace All

Then click on Data | Text to Columns, then choose delimited, Next, Space, Next, then Finish.

Finally, you can highlight column D and use CTRL-H to replace the underscores with a <space>. Your header might be slightly misaligned because of "Server Mgmt".

Hope this helps.

Pete

Thanks Very much Bebo, this does work and is definitely on the lines of what I am aiming for, however some of the results are not quite as expected:

My apologies. Where I have TEST SITE 1 this needs to be in the same cell for example, Newcastle Road

Also, I didn't want to put all of the column headers in my initial posting, but there are roughly the same amount of column headers again. Would I be able to amend your formulae for use with the additional column headers easy enough?


Many thanks for all your help

David

My apologies Pete, I got an email alert from Bebo and didn't see your reply.

I have used your method and it has worked nicely for a single row i.e. the column headers,but what I ned to do now is split the data under each of these column headers into the individual cells under the said column header. The values are alldifferent with spaces withing the data within the cells.

Any ideas how this could be achieved? I am looking for something genericideally so that I can apply a formula no matter what the data is within the Cells. I'm probably being optimistic


Thanks again

David

Hi David,

it would help if you could attach a workbook with some test data in, so that we can see the variations in data that you encounter. The FAQ describes how to attach a workbook to one of your posts - basically, click on Go Advanced while in Edit mode, then scroll down to Manage Attachments, then Add Files, Select Files, (navigate to your file and double-click it), Upload Files, then Done and Submit Reply.

Hope this helps.

Pete

Thanks Pete, the data really is random.

I have attached a dummy workbook with around half of the column headers and some random default data representing what I am actually looking at.


Thanks for your help

David

It looks like the data has already been split out into separate columns on that file - can you send an example where it is all in one cell per row?

Cheers,

Pete

Apologies, that's how it is supposed to look when

Please see attached, thanks

How many different sites do you have? Those are the things which will cause the problems as they have extra spaces in them which we need to change to something else. It is possible to list the addresses in a lookup table and then get them changed to something not containing spaces, eg

1 High Street, Manchester

gets changed to:

1_High_Street,_Manchester

so that we can then use <space> as the delimiter.

Hope this helps.

Pete

Hi Pete,

I have no problem with us changing the values from 1 High Street, Manchester to 1_High_Street,_Manchester but there are hundreds of unique site locations and other columns have multiple words with spaces also.

Cheers,
David

I think in that case your real data is much more complex than the example you set out in Post #1, so Data | Text to Columns after Find & Replace a few times is probably not the appropriate method to use.

Pete

Thanks for your help anyway Pete, I may be able to extract the information from the original backend database which will retain the spacing and columns. Thanks again for the effort

David