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
Solved

Excel text and number format, cant get all the same

Posted on 2014-07-23
5
277 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying 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

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

861 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