Delimiting when seperator is a comma & number is bigger than 1,000

[Guest]

I have a data download that is appearing in the column like this:

1021,105,1268

I am trying to delimit it to appear in 3 seperate columns like this:

1021 105 1268

But excel is reading the , as a thousand seperator & therefore treating the
cell as a whole number ie 10211051268

Is there any way I can overwrite this, or another way I can seperate into
columns? The reason I need to is to do a vlookup on another sheet as the
above refer to product codes & I need to report on total sales for all
products.

Any help gratefully appreciated!!

Anna

 

[Bernard Liengme]

1) can you change the app that exports the data to add a space after the
comma?
or
2) can you get the exporter to add a character (such as single quote) at the
start of the string?
or
3) if the numbers are always 4 digit, 3 digit, 4 digit you could format the
cells as text and extract using Fixed option.
best wishes

 

[Guest]

1. Select the cell, convert it to text (Ctrl + 1, select the Number tab,
select Text).

2. Select Data | Data Tools | Text to Columns - this open the Text to
Columns Wizard.

3. Select the Delimited radio button, click Next, check the Other checkbox
and input a , in the textbox to the right (you should now see the 3 columns
in the Data preview at the bottom of the dialog box - this tells you it is
has worked).

4. Click on the Next button, and now you can apply a specific
numeric/text/date etc. format, column by column. Once done, click on Finish
to view the results.

 

[Guest]

Can't do any of those unfortunately because I can't change the exporting
process & the number format varies. But I have discovered if I copy it into
word & then do a find & replace and change the comma to a different character
that seems to work. would be easier if I could do it within excel though.

Thanks for your help!

Anna

 

[Guest]

I did try that, but it doesn't work for numbers bigger than 3 digits as it
sees the comma as a thousand seperator & the whole cell as one number, rather
than 2 numbers seperated by a comma. The only way I can see is by putting the
data into word & doing a find & replace there, changing the seperator to
something other than a comma. Seems to work fine after this, but would be
better if I could do it all within excel.

Thanks for your help

Anna