Excel text and number format, cant get all the same

Posted on 2014-07-23
Medium Priority
Last Modified: 2014-07-23
I have a price list that was created by exporting part numbers from SQL via Access, this was copy pasted to excel, some of the part numbers end with trailing zero's 0712.03000 for example. those numbers exported and put in this excel file are ok for what we are trying to do, but at one point the excel file had some manipulation and the part numbers were manually typed 0712.03000, the numbers from the import all seem to be format as number with decimal at 5, but all have the green triangle icon except where someone typed in the number.

No matter how I try to format the cells when I try to concatenate adding a "REG" prefix, the trailing zeroes get stripped.

I have tried formatting the entire column to numbers, or text, still get the same results, I cant seem to force the formatting on those cells.
Question by:Jeremy Kirkbride
LVL 19

Expert Comment

ID: 40214140
Im a little confused.

what is the end result you are hoping for?

are you saying that you're getting different results based on where the number is coming from?
sounds like on of your options is coming across as a string
LVL 19

Expert Comment

ID: 40214160
try to make selection of desired cells, copy them, then right click and paste special as Values.
Also you can format some empty column as Text and make paste special>Values here to check if it will work then as you wish
LVL 19

Accepted Solution

Montoya earned 2000 total points
ID: 40214179
You can also do this:

This method works best if the data is arranged in a single column or row. This method uses the TRIM function and the CLEAN function to remove extra spaces and nonprinting characters that may be imported with the file. The following example assumes that the data is in column A and starts in row 1 ($A$1). To use this example, follow these steps:
Insert a column to the right of column A. To do this, right-click Column B's header, and then click Insert.
In the first cell of the inserted column (B1), type the following:
In column B, select all the cells to the right of the cells that contain data in column A.
On the Edit menu, point to Fill, and then click Down.

The new column contains the values of the text in column A.
With the same range selected, click Copy on the Edit menu. (In Excel 2007 or later, click the Copy icon on the ribbon.)
Select cell A1. In Excel 2003 or earlier, click Paste Special on the Edit menu. In 2007 or later, click the down arrow under Paste in the ribbon, and click Paste Special. Under Paste, click Values, and then click OK to paste the converted values back on top of column A.
Delete column B by selecting the column and clicking Delete on the Edit menu.
The text that was in column A is in a number format.

Note This method may not remove all nonprinting whitespace characters. For example, the whitespace character Chr$(160) will not be removed.
LVL 37

Expert Comment

ID: 40214342
How did you try adding REG?

Did you try using the TEXT function, or if you are using code Format?

="REG"&TEXT(A1, "0000.00000")

Author Closing Comment

by:Jeremy Kirkbride
ID: 40214734

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question