Solved

Excel text and number format, cant get all the same

Posted on 2014-07-23
5
279 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 34

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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…
This collection of functions covers all the normal rounding methods of just about any numeric value.
Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

734 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