Delimiting when seperator is a comma & number is bigger than 1,000
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.
--
Gnothi se auton.
"Anna" wrote:
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
|