Solved

Excel text and number format, cant get all the same

Posted on 2014-07-23
5
273 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:Iammontoya
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:
Iammontoya 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

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…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Viewers will learn the different options available in the Backstage view in Excel 2013.
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now