[hotelguru]

I'm using Excel under Office XP in Windows XP and I'd like to know if there's a formula for dividing a cell that has three different values in it, into three separate columns?

It looks like this:
1 3% xyz

I'd like each value to be in a separate column.

Please help.
Kind regards
Hotelguru

 

[XL Guru]

There's formulas, but with that setup you might be better looking at Data -- Text to Columns. With space as delimiter.

Rgds,
Andy

 

[hotelguru]

XL Guru thanks. That worked fine but now I have another question.

I have 3,892 rows of data spread over columns A - N.

I actually need columns A & B separate but the data in columnns C - N I need in one column.

Although I've been trying to figure it out myself I can't get it right.

Can you help me?

Kind regards
Hotelguru

 

[maxflia10]

I know Andy doesn't like addins, but with this addin,

http://longre.free.fr/english/index.html

it becomes,

=MCONCAT(C1:N1," ")

with an Excel function,

=C1&" "&D1&" "&E1&" "......etc

please note the space between the quotes, if you don't want spaces omit the " " from both formulas.

 

[hotelguru]

Thanks for the tip, I'll try it out and let you know how I get on.

Kind regards
Hotelguru

 

[XL Guru]

Hi maxflia10!

>> I know Andy doesn't like addins

Probably I've not expressed my views on this effectively, since I'm currently running ATP (obviously) and John W's Enhanced Data Form and Chip's Symbolizer and Ron's GoogleSearch.

Laurent is a very smart guy, way above my level. Notwithstanding, I just think it's easy to get bogged down with add-ins if you're not careful.

Best rgds,
Andy

 

[hotelguru]

Thanks for your input. I downloaded the add-in but I couldn't get it to do what I wanted it to do.

So I asked at a colleague and below is a bullet-point style expalnation of how I solved it.

Just so that it's clear exactly what I was trying to achieve I'm writing it here:

TASK
I wanted to split text/data in a .csv file in one column into separate columns but keeping phrases together

EXAMPLE
COLUMN A
20846 6.49% silver widgets
8296 3.74% discount silver widgets
3628 2.58% long silver widgets

a). Highlight the column for which you want the data merged (just the data or the entire column)

b). Select "DATA" at the top and then from the drop-down menu select "TEXT TO COLUMNS"

c). Select "FIXED WIDTH"

d). Click on "FINISH"

RESULT
COLUMN A COLUMN B COLUMN C
20846 6.49% silver widgets
8296 3.74% discount silver widgets
3628 2.58% long silver widgets

Once again, thanks for your input and if any of you ever need a
hotel, I'll be happy to help you. I run a London, UK hotel reservations website www.london-discount-hotel.com (hence the username).

Hotelguru