Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Quick Way To Format Text To Number In 07

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Had a pivot table mess up this AM due to a V-lookup not working as some cells in my worksheet were formatted as text instead of number. You'd think that highlighting the whole column and reformatting it as number would fix them all or painting the format from a "correct cell" would work but it doesn't. I had to one by one correct all the cells that were in text instead of number format.

Has to be a better way... please point this Excel handicapped accountant in the right direction!


View Answers     

Similar Excel Tutorials

Excel Time - Summing Time Greater than 24 Hours
I'll show you the easy and simple way to sum time past 24 hours in Excel.  All we really have to do is to change th ...
Keep Leading Zeros in Numbers in Excel - 2 Ways
I'll show you 2 ways to add and keep leading zeros in front of numbers in Excel. These two methods are very simple ...
Excel Formatting for Zip Codes and Phone Numbers
Display numbers in Excel as zip/postal codes and phone numbers in order to make them easier to read and retain any ...
Convert Numbers Stored as Text to Numbers in Excel
I'll show you 4 ways to convert numbers stored as text to numbers in Excel.  This situation often happens when you ...

Helpful Excel Macros

Format Cells as a Percentage in Excel Number Formatting
- This free Excel macro formats a selection of cells as a Percentage in Excel. This simply changes a cell's number format
Format Cells in The Number (Numerical) Number Format in Excel
- This free Excel macro formats selected cells in the Number or Numerical number format in Excel. This means that the cel
Format Cells as Time in Excel
- This free Excel macro formats a selection of cells in the Time format in Excel. This Time number format means that inst
Format Cells as Text in Excel
- This free Excel macro formats a selection of cells as Text in Excel. This macro applies the Text number format to cells
Format Cells in The Short Date Number Format in Excel
- This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display

Similar Topics

Good afternoon. I have a field in my spreadsheet that is six-digit client numbers that may have leading zeroes. For that reason, I have to format it as text. The data is coming from different sources, so some of the cell entries are listed as numbers, some as text. I am using this information in a pivot table, but because of the difference in format, all the cells are not showing up. I am not sure at this point if it is the text that is not showing up or the numbers. I have tried format as text (but I understand that will not work with cells that were not formatted that way when you first entered the data), then Text to Column (as it shows in a Mr. Excel video); I also tried to concatenate an apostrophe with the number, but it did not get rid of the formula (it returned "=concatenate(A2,B2)" instead of a value) - I am at a loss! We have to keep the leading zeroes, but unfortunately, I am in a time crunch and need a solution - thank you for your help!


Stumped on how to take information from column B which contains a manual number that's supplied by a manufacture in the following format EN12345678901234 and change it into a format that our system currently uses to look for changes to these manuals. Our format drops the EN and the numbers are formatted like this 12-34-56-789-012-34. I want to put the reformatted info in column D. I've tried the custom feature in the cell format but it doesn't actual change the info it just masks it to look correct. Any suggestions to make this happen?

I have a revenue tracking file, which i want to track data over a set number of weeks (13) it select data from a pivot table using the Get pivot command. the data checks against a week number (format= 201026) from one of the cells in the row above.

However the cells used for week says that its in 'text format or has an apostrophe before it'. I want to have these cells increment based on a starting week that i would enter to indicate first week to track from but in when I convert to a number it won't find the data from the Pivot, and the same when I try to increment from a cell that is in text format too.

Any ideas where I am going wrong?

Hi Folks,

Is there an automatic way to convert a cell that is formatted as text to being formatted as a number and have the text that is already in the cell take on that format.

I frequently import data. Because the columnar data being imported isn't consistent, I must import the data as text so as not to lose important information. Then, I often have large portions of the SS that are actually fractions or dollar values. I want to re-format those cells to be a number format. The only way I've found to do it is to change the format, and then manually select each cell, hit F2 to enter the cell, then hit enter. When I do this, the data in that cell takes on the new format. But I have thousands of cells and I'd really like to not have to touch each cell. Any ideas?

I'm using Excel 2000.
Thx much, Sandy


I am getting a generated report from a customers website that i wish to extract data from
The column i wish to work with is an ID eg. 10001702 that i wish to use in a VLOOKUP.

The problem is the number is in an odd format, text or something so when i try to VLOOKUP it cannot recognize the number
Left justified like text instead of right justified like a number
clicking format cells and changing to number does not solve the issue

if i click on the cell containing the number, then click on the formula bar, then press enter, it will become a number. While this works its still a tedious method.

Is there a way i can find out how the number is formatted so i can set the vlookup to search for that?
is there a way i can add another column to in which it converts the number into a number format.
or any other solutions?

Hope people can follow that.

Thanks for any help

A colleague has an excel spreadsheet, written by someone else, that has some cells formatted as text. All these cells contain numbers.

We want to link them into a database that retrieves the numbers and places them into these cells, as a formula. The problem is that it seems impossible to change the cells into general or number format, and moreover, any further formulas based on the contents of the cell don't work unless the number is entered as text.

So, we have, currently:

1 2 3
A 115034325 =lookup(a2, $database1,2)

If we link it in:
1 2 3
A 99 =lookup(a1, database2,9) =lookup(a2, database1,2)

on screen it appears as:
1 2 3
A 99 =lookup(a1, database2,9) =#N/A

Cell A2 is formatted as text. Any formula, (e.g. =A1) still displays as if it was a formula.

I have a cell that contains "=D2", for example. It is formatted as Number and shows the numerical value in D2. I want to change the number format to Text so that "=D2" appears. It works, but besides changing the number format to Text I also have to double click in the cell for it to take effect.

When I go from one number format to another, I just have to click off the cell for the new format to take effect. Not so with changing to Text. I'll actually have a lot of cells that I'll first need to change to Text and later need to change back to Number, so I can't double click in them all.

The other option, I guess, would be to have a macro that adds an apostrope in front of all formulas (everything that starts with "=" in columns A through D and another macro that removes the apostrophes. I'm not sure how to do that.


I have a cell containing a vlookup that returns a value. Depending on the lookup parameters the value returned could be a percentage or a normal number. How do I change the format the value is displayed depending on the lookup used (which is contained in a separate cell)?

Conditional formatting doesn't include the Number format, only Borders, Patterns etc. I know I could use the text() function to convert the value to text in either a percentage or number format, but I want to use the value in a graph, so text is no good to me.

It's not really appropriate to write any VB code for this situation.

Any ideas?

I am experiencing an issue with number format in excel 2007 small business version. Whenever I store any number with decimal point it consider it as a text, for example I enter 2.50 it stays left aligned and when we enter other numbers and try to sum the numbers it does not give correct result as it does not consider the numbers with decimal point as number but instead considers them as text. If I enter a number without decimal point it works fine.

Moreover even if I change the decimal number format to number it still does not change to number format and it remains left aligned as if it were a text. I have tried to uninstall MS Office and then reinstall it again but still the problem persists. Please advice what could be a possible reason for this issue...

I am attaching a specimen of the file with issue...

I want to be able to validate that there is a correct SSN, meaning no letters, the correct format. I tried the following but it is not working.

Private Sub tbSSN_AfterUpdate() 
    tbSSN.Text = Format(tbSSN, "### - ## - ####") 
    If Format(tbSSN, "### - ## - ####") = False Then 
        MsgBox "Incorrect SSN format" 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

Now the first part of the converts a 9 digit number to the correct format, if the customer inputs 9 numbers. But, there is nothing to stop them from inputting letters or the incorrect number of digits.

I am working on a macro that loops through a bunch of cells in an Excel 2007 worksheet. It would be most convenient if I could determine for each cell what is in the cell, where the choices are 1) a number formatted as text, 2) a number, 3) a blank, 4) text. The flow in the loop goes differently depending on the answer. Identifying numbers and text is easy, but I'm having trouble figuring out how to tell whether the text is actually a formatted number. Can I set up some error handling, then try to convert the text to a number? Is there a better way?


I have a cell that has this number format: "VERY VERY VERY VERY LARGE TEXT" 0,0000000000%.
I need the text to be there to understand what the cell means, and I need it to be a number because other cells use this cell's value to perform calculations.

If I let the column fit to the full size with the number in it, it displays: VERY VERY VERY VERY LARGE TEXT 3,5000000000% If I put the the whole text (TEXT, not number) in a cell with a smaller column width and select for the cell alignment: Text Control, Wrap text to be checked, it displays: VERY VERY VERY VERY LARGE
TEXT 3,5000000000% However if I select wrap text in the cell that has the number and the number format, wrap text does not work (what might make perfect sense cause now it's actually a number), and it displays: #################### Is there any way to get it displayed as I want, and also having there a number and not a text?
TIA & Regards ...

I have a CSV file that I will open (or import) and which has one column that contains serial numbers. The problem is that some of the numbers have this format: 123E567.

Excel promptly converts this text to a number: 1.23E+569. Even if I then change the format of the cell to text, it still keep the text 1.23E+569.

How can I either prevent the original text from being converted to begin with or how can I correct it after the fact?

Best regards,

I uploaded this file to help show you the problem,

Currently i have the whole column formatted to text, . All my vlookups work except for a sporadic few ( not sure why ). On the ones that dont work it displays N/A in my adjacent cell's which contain vlookup formula's and i have to format the cell to a number for it to work. I need a way to format the whole column to where it works on every vlookup. I have thousands of row's and its tedious finding every N/A and converting it to a number.

So again having everything formatted to text produces errors in some vlookup's,

If i format everything to a number it works but i lose the zero's which cause errors / na's in every part number that lost a zero.

I have spent hours this afternoon in Excel 2003 trying to concatenate two adjacent text columns into a third column defined as Text format. It doesn't work, the result cell just displays the formula you enter {e.g. =A1&B1 or =CONCATENATE(A1,B1)}.

I discovered after a great deal of frustration that this will only work if the cell containing the formula is formatted as 'General'.

All the MS command help refers to the data being concatenated as 'text' data as does the command help that displays as you type.

I found no help on this on the MS site and trawling the web came up with nothing either.

It seems incredible to me that a standard text operation like this will only work in a format of 'General' which is a pretty non-descript format anyway - 'General format cells have no specific number format' is the helpful description you get when applying this format to cells in Excel.

...or maybe I've missed something. Any help gratefully received.
Many thanks.

Hi all,
I think this question really gets at a larger issue but here goes:

I'm doing a conditional format of a cell and I'm just comparing it to the cell next to it, and highlighting the cell if it's different than the one next to it. IE, in A1 the condition is <> A2.

It doesn't work...the issue seems to be this ongoing thing about what's a number and what's not. Cell A1 and A2 are both formatted as Text but *currently* contain only digits, ie, A1 = 1033 and A2 = 1033 but the A1 still gets highlighted.

I tested by changing the condition to <> INT(A2) and it works. Since these cells may contain alpha, I can't use INT() reliabley and I don't want to get into the hassle of using IF(), etc. within the format logic--I guess if I have to then I have to, but I get to a point and think "There's got to be an easier way". do I get Excel to simply compare the cells as I have explicitly formatted them without writing a hundred lines of code in a formula that simply compares one cell to another? It's as if Excel is looking at numeric data as numeric--but only on one side of the comparison, it's not consistent in it's annoying tendencies.

What is the best way to get around this?


I am using XP and Office 2003.

I have the code below that highlights numbers/cells in a range the same highlight color if the numbers match from another range.

Sub FindHighlight()
Dim i As Integer, c As Range

If Range("A1") = "xyz" Then
For i = 1 To 8
For Each c In Range("C1:C22")
With c
If .Value = Range("J" & i).Value Then .Interior.ColorIndex = Range("J" & i).Interior.ColorIndex
End With
Next c
Next i
End If

This code works fine if the "numbers" in both ranges are actually formatted as Number or General. My problem is, sometimes the numbers are Text. This code is part of a tool that runs against an excel output sheet that I don't have control over. I don't know why some of the output sheets have numbers formatted as text. I tried to change the format to "number" and "general" but that doesn't seems to work as an easy fix. (I have had similar problems where a number is stored as text and a simple format change doesn't seem to work, even though the format is changed to "number"??) I did try to Edit; Clear:All, then replace the numbers, and that then works, but I don't want to get into a lot more code to do that if there is an easier fix.

Is there a way to modify my code ( I guess this part: If .Value = Range("J" & i).Value) so it will recognize the number stored as text, as a number, so that it will work properly?

If it matters, the "numbers" in Range("C1:C22") are the ones that are sometimes text.

Hope this is all clear.

Thank you for any assistance...Dean


Is it possible to custom number format a cell based on text?

For example, I have a column with a bunch of people names.
What I want is based on the name written, the cell will only allow a 2 digits number or a 4 digit number.

I tried something like =TEXT(A1;"VLOOKUP(C1;$D$1:$E$10;2)"), but I had no luck.

I thought using the VLOOKUP so I could use the format "0.00" and "0.0000" for each person name, so the text function would recognize the format, but apparently I was wrong.

Any help?


I have two columsn: Item UPC numbers and descriptions.

=IF(B15="", "", VLOOKUP(B15, products, 2, FALSE))

I am using the Vlookup formula to pull the descriptions.
I have formatted the cells as Text, because I want to
keep the zeros.

If the UPC starts with zero, there is no problem.
But if I enter an UPC that starts with a non-zero then I get
the Value Not Available Error.
The big exception is item number 13146.I have no problems pulling
the description using that number, even though is formatted just like the others.

In order for the formula to work I have to format the UPC
as "General" on both sheets(for the non-zero UPC). But that's is time consuming. I cannot format the whole column as General, because that eliminates the zero.

I've tried to copy the format of item 13146 unto the other items
But that did not work.

At this point, I am thinking that Excel keeps a number format
even though I set the Format to Text. I am not sure how to test
for this, so right now is just an idea.

Anyone has any ideas, I am willing to try anything at this point!!!

I was going to attach the file, but I can't. Something about Uploading Error:invalid file.

Book1.xlsxI'm hoping someone can help me understand how to change cell format from Text to Number. In general, I know how to do this. However, what I "thought" I knew is not working here. In the attached file, you will see a list of numbers in column A. You will also see that each cell has an error indicator stating the number is stored as text. When I hover over the cell and select the error indicator drop down, I can select the option to convert the text to a number. This works just fine... on a row by row basis. However, I have approximately 80,000 rows in the worksheet and cannot possibly perform this process on a row-by-row basis. So, I highlighted all the cells in the column, right clicked and selected Number. The cells display as a number, however, they appear to be still stored as text with the error message indicating text. How can that be? I don't know what other options are available to me to get all of these rows converted to true number formats. Can someone please help? Thank you!

Hello, this is my first time posting here, although I've learned a lot from reading this forum and have found it very helpful to do searches for information before in the past.

I have a question, though, that I couldn't find by searching through other posts and have been stuck on this problem for days.

I have a row in a worksheet in which the value in cell D4 (and D5, D6, D7, etc) contains a string of text. If part of the text in that cell matches a value I specify, I'd like to format a corresponding cell in that same row to be either a percentage or a whole number, depending on the text.

For example, if a cell in column D contains any part of the text "trade," I'd like to format the cell in column F as a percentage. If the cell in column D contains any part of the text "media," I'd like to format the corresponding row's cell in column F to be a regular whole number with 2 decimal places. The text I'd like to use will not match exactly, though, to the text string in column D. Ie, column D might say "target trade item PPG," but I'd like to use this formatting if any part of the text string in column D matches "trade."

Is this possible? I know conditional formatting doesn't work since obviously you can't format cells as percentages, whole numbers, etc, using this. I've also tried various select case statements, but can't get those to work where the text matches only part of the text in column D.

I'd ideally like this code to work in VBA as a worksheet_change event. Any help getting me on the right path would be greatly appreciated; I've been really stumped. I can provide more details if needed. Thank you,


My first question is, is there a way to highlight a whole column to change the data from text to a number? When I initially download the data into the Bank Data Download tab, the information is in a text format, I believe for formulas to work, the data should be in number format, correct? Is there an easy way to select a whole column and change the format? If I highlight a column and do the format cells and change the data to a number, it does not take effect unless I click on each individual cell and click Enter. Is there any easier way?

The second part of my call for help is I would like for the data to flow from the Bank Download tab, into the Disbursement Summary tab. The identifiers would be the Customer Reference Number, column N of the Bank Data Download tab, and match to the corresponding day and column in the Disbursement Summary tab.

If you have any questions or need me to clarify anything please let me know.

Thank you for your help.

I have a spreadsheet that has about 5000 entries, the date is formatted Eg 29/07/07 11:21 I change the format using custom formatting to Jul-07 but when I try and use it in a pivot table it separates each individual Jul-07 cell based on 29/07/07 11:21 format, if I try and change the format number to text it displays the number Eg 38776.77865 how can I achieve the simpler format of Jul-07?



reality.sys not found
universe halted

In the old versons of excell I was able to format a number such as a cell number to start with 0 and then break the number down to have a space after the third and sixth digit via the custom text field in format number using 0## ### ####. Simelarly I could achieve the same effect with text(A1,"0## ### ####"). Now the result is "0 ### ### ###. I cant get rid of the space after the zero and make the number look like a cell number anymore. Any solution?

I have a problem when I read the ":" colon as a separator from an excel or
text file that uses it as a separator.

the example I have is that when the colon separator is followed by a
number, say for example : 35, the number appears on the cell as 35:00:00
which is a time format.

It seems that Excel takes the separator as a time format for the cell and it
does not allow change of the cell's format not even to a simple text format.
Even when the formatting of the cell is changed to a general format it
generates a decimal number that is a multiplier of 24. If the value in the
cell is ": 24" and we format the cell as text it would show a"1" in it.

Is there a way the maintain the cell as a text formatted cell where the data
is maintained the same even if a colon is used as a separator?

In other words if ": 24 " is entered the cell should stay as ": 24" after a
text format is applied.