Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delimiting when seperator is a comma & number is bigger than 1,000
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delimiting when seperator is a comma & number is bigger than 1,000
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 -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Anna" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delimiting when seperator is a comma & number is bigger than 1
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 "Bernard Liengme" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Anna" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delimiting when seperator is a comma & number is bigger than 1
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 "rsantaro" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to Calculate how much bigger one Number is than another in | Excel Discussion (Misc queries) | |||
Excel 2002 -Merging cells contents in with a comma seperator | Excel Discussion (Misc queries) | |||
Len function returns bigger number | Excel Discussion (Misc queries) | |||
Excel 2000 (9.0.6926 SP3) spreadsheet getting bigger & bigger .... | Excel Discussion (Misc queries) | |||
Insert a Seperator into a number string. | Excel Discussion (Misc queries) |