Solved

Excel text and number format, cant get all the same

Posted on 2014-07-23
5
276 Views
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.
0
Comment
Question by:Jeremy Kirkbride
5 Comments
 
LVL 19

Expert Comment

by:Montoya
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
0
 
LVL 19

Expert Comment

by:helpfinder
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
0
 
LVL 19

Accepted Solution

by:
Montoya earned 500 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:
$B$1:   =VALUE(TRIM(CLEAN(A1)))
                              
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.
0
 
LVL 33

Expert Comment

by:Norie
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")
0
 

Author Closing Comment

by:Jeremy Kirkbride
ID: 40214734
Thanks
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Office Web Apps/ SharePoint 2013 1 84
Flashing Formula Excel - Caused by VBA? 7 72
Skype/Lync Emoticons Question 3 106
Display multiple images in report 12 119
I wanted to share this with fellow Experts, who might not know. How often have you wanted to learn something, only to be set back by either restrictions imposed on "trial" or "evaluation" software?  How often have you had to rebuild a home networ…
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…
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…

803 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