Converting Forced Text to Numbers

I'm using "Version 2203 (Build 15028-20204 Click-to-Run)"... perhaps there is a difference between our versions.

If not, or if any difference doesn't matter, perhaps there is a setting that affects this behavior. It's hard to imagine one from my knowledge of the settings, but definitely not impossible.

There is also the kind of difference that is due to one's usage of features and they can be subtle. For example, copy something to the Clipboard that includes spaces and then paste it into an Excel sheet. Likely it will paste as lines of text more or less matching its form in the original material. Then use the Text-to-Columns feature (on something else, say) and specify, oh, "Space" as a second delimiter along with the default "Tab" (either finishing the T2C or escaping after selecting the "Space" delimiter choice). Copy the same thing as before and paste it now. Almost always (almost) you get a very different paste result in that while you will see the lines maintained, you will see each line's text split into cells at the points where spaces exist in the original.

That's a VERY noticeable weird change/difference, but for all I know, there are dozens of similar things. I doubt there's one for this situation, but... like before, who knows? At least until something like this gets someone to delve into it.

However, since the "0" is not needed, why bother with it and have any possible issue?

@Roy,

You said "Presumably, the leading "0+" (not the one related to the MID() function) is meant to coerce Excel into NOT doing the above. But it fails to do so. The format still changes to text and recalculation still ruins everything" but, unless I am misunderstanding you, does not happen in my copy of XL365... my formula leaves the cell it is in formatted as General and the number in the cell is a real Excel number (right aligned).

@Rick Rothenstein:

Using "0+" in the testing of each element of the array of values the MID() produces is great. Everything I've ever read says that since it is an arithmetic operation, it should be faster than a function (the VALUE() function I used for the purpose), even blindingly faster. May not be needed in a given use, but it never hurts either, eh?

Personal preference? I take TEXTJOIN() over CONCAT() any time just because it's a wee bit more flexible and if you use something other than "nothing" for the delimiter, it does not add one of them to the end which then usually needs removed. Its form for all that is simple and intuitive (though I'd've gone "Range-Delimiter-Empties" myself as being MORE intuitive were I its author) where anything with CONCAT() usually has an oddity requiring careful thought.

However, it doesn't come down to personal preference.

Using TEXTJOIN(), the output is, of course, text. However, the cell retains its starting format which certainly should not be text. So if you see the cell recalculated for any reason, its output remains the same and all downstream uses of it remain as they were, unaffected by the recalc.

Using CONCAT() though... The format for the cell is changed, literally changed, to text. Recalc that cell and the formula becomes text, not a formula since that's what happens to formulas entered in a text-formatted cell. No bueno.

Presumably, the leading "0+" (not the one related to the MID() function) is meant to coerce Excel into NOT doing the above. But it fails to do so. The format still changes to text and recalculation still ruins everything. I tried it with just the "+" as that is often sufficient, and it does leave it as text (ISTEXT() gives TRUE) but does not change the cell number format TO text.

Using VALUE() to wrap the whole thing instead does work in that it keeps the cell format from changing to text. But the result is a value and while that would usually be MY need, it may not be everyone's.

By the way, in my earlier diatribe about people shunning helper columns and VBA, I mentioned a bit of it being like considering "Boy, I need a third hand" an insult to God. Credit (some credit anyway, er, barely some...) where credit is due: can't remember the author or story title, but it came from a Hugo award winner in the 1960's in which a post-nuclear apocalyptic world apparently had radiation-driven mutancy and those without mutation had gone over the edge into hardcore religion (Christianity of course, not, say, Norse mythology) and considered mutants to be abominations, no matter how nice a pack of folk they were, to the degree that even a careless comment like the above would result in savage punishment as if the person were wishing to BE an actual abomination and therefore an insult to God... Etc. Hijinks ensue, some psy-capable young folk must flee, more hijinks ensue... and 50 years later it still sticks enough to be a working metaphor for me. Just can't remember the author or story name... sigh...

@Roy,

This shorter formula also works...

=0+CONCAT(IFERROR(0+MID(A1,SEQUENCE(LEN(A1)),1),""))

@Roy,

This shorter formula also works...

=0+CONCAT(IFERROR(0+MID(A1,SEQUENCE(LEN(A1)),1),""))

The following formula will strip out JUST numerical characters and turn them into a single value. It DOES ignore numerical assistants like "-", ",", and "." as well as fellow travellers such as "(", ")", "_", and any currency markers. But there is a slightly different approach formula that keeps anything of that sort using a list you create or populate:

=VALUE(TEXTJOIN("",TRUE,IFERROR(VALUE(MID(A1,SEQUENCE(1,LEN(A1)),1)),"")))

and one can use LET(), as in: "LET( Value, A1, etc.)", to allow that A1 which occurs twice to exist in only the one place at the start of the formula allowing easy editing of the cell referred to. (One place, easy to locate, no hunting through 23 lines of formula looking for all instances of A1 or using Find & Replace only to find out the string A1 occurs in other ways in the formula.)

The cell reference is in the MID() so using a range gets one single reallllly long joined value (so if summing 6 numbers, each 3-4-5 digits long, one would ahve a resulting single number of 18 to 30 digits rather than six separate numbers). So it would have to be copied down a column or used multiple times in a formula rather than as a simple SUM() formula. Though one might get creative with string building techniques and something like OFFSET() to ease that pain.

However, if not willing or able to hunt down the offending extra characters in one's "unclean" text import, this will clean it with just the existence of a helper column.

(For those who hate helper columns, heck with you. Do the cleaning work or sit, spin, and whine instead. Sick of people who look down on some tools. I get "hard to understand, learn, or use" like a whole macro language but "ew-w-w-w, I hate helper columns"... no. (Not that slowly learning a macro language useful bit by useful bit isn't a good way forward on that front, but some won't even learn simple uses for the Immediate Window 'cuase macro is macro is macro and they ain't gonna do even useful bits of learning where those are concerned. They'd as soon "insult God" by saying "boy, I wish I had a third hand right about now" as to learn any piece of VBA.))

Thanks, this worked!

Thank you! I've been looking for this answer for 30 minutes - you solved my problem!

Best solution on the internet.
Thanks

Thanks!! This definitely helped!!

Just what I needed to explain the issue with some missing values from a cumulative of a row - Thanks

I am creating a macro to clean up data in a spreadsheet. One of the columns I convert to numbers, but when I run my macro all the steps are executed accept the convert to number column. How can I incorporate this step in my macro?

It works for me. I have lots of number data that got moved as text. This help convert them back.

This tip did not work for me.

Thanks Allen, very useful ! Saved one of my users a lot of manual work.

Ramesh

This sounds like homework. Computers display 15 characters for integers and up to 20 for decimals. Anything else has to be shown as text. The calculation has to be done as in primary school and numbers are carried over into the next column.

Say you enter 20 single digits into columns and copy them down. Add them with the following

Cell U3:= =(SUM(V1:V3)-V4)/10
Cell U4:= =MOD(SUM(U1:U3),10)

Now you have to concatentate all the cells to get your (text) answer. I used this UDF
=JOINCELLS(C4:V4,"")

The code for this is:
Function JOINCELLS(ByVal ref As Range, _
Optional separator As String = ", ", _
Optional include_blanks As Boolean = False, _
Optional Order As Integer = 0) As String

Dim c As Range, str As String, X As Integer

X = Len(separator)
Select Case Order
Case Is = 0
For Each c In ref
If include_blanks = False And Len(c) > 0 Then
str = str & c & separator
ElseIf include_blanks Then
str = str & c & separator
End If
Next c
GoTo ReturnJoin
Case Is > 0
For Each c In ref
If include_blanks = False And Len(c) > 0 Then
str = c & separator & str
ElseIf include_blanks Then
str = str & c & separator
End If
Next
End Select
ReturnJoin:
JOINCELLS = Left(str, Len(str) - X)
End Function

(see Figure 1 below)

I want the result in 20 numbers in Excel.(A2 =20 numbers and A3 =20 numbers A1+A2=A3)
I have taken A1=Text A2=Text Data type
Maximum numbers Excel is taking upto 15 characters.
when I add 2 numbers I am getting upto 15 numbers the other 5 numbers are displaying as '00000'

Thanks In Advance
-Ramesh

"This works because Excel multiples each cell in the range (step 3)"
It should read "Excel multiplies".

Is there a way to format a cell that has text into a number and afterwards sum those number?

For example L in one cell
Next cell has 50, which represents L
And if I have two Ls it would calculate 100

Thank you, the multiply tips work for me. Is the a way to enable that diamon exclamation mark? I seems to dismiss it the other day

$ Sixty Six Million Three Hundred Sixty Four Thousand Three Hundred Thirty Four Dollars and Twenty Two Cents AND Thirty %

It is excellent site to get useful tips.

Hi ... i am having difficulty with what you mentioned above. i have used the =ISTEXT formula and it confirms that the value is a text but no error icon appears for me to click Convert to Number option. Also the value in the cells are left aligned confirming it is text. I have used the the Text to Column wizard, also used paste special to ADD 0 or multiply as seen on youtube videos but nothing seems to work. How do i solve the problem of converting them to numbers ... Thank you for your help

my excel report don't have this error checking "Converting Forced Text to Numbers".

Is the any setting problem?

Thanks for the provided solutions..

Thanks for this solution, it saved me hours of pain!

I have two columns that are supposed to be currency, but none of the entries show as such. The values are in a table, if that matters. It is 2007. Also another column shows the error, "Inconsistent calculated formula" although I don't have formulas and the columns are formatted as text. I cannot seem to clear the errors either. The spreadsheet is a form I did not create, but use to store data.

None of your solutions is working. You need to acknowledge that they may not. If you don't know everything, you don't, but don't be dogmatic.

Your tip is INVALUABLE and saved me some time, I appreciate it very much. Keep up the good work.
Greetings from Milan, Italy

Unable to sum the values for the mentioned.
please resolve.

 5,000.00 
 1,950.00 
 5,000.00 
 3,000.00 
 1,400.00 
 5,000.00 

tried many ideas like find and replace,text to columns etc.

Copying columns from some text files, e.g. HTML results in an extra character inserted AFTER the number in a text format which resists conversion to a proper number.

Get one of those cells up in the formula bar and place the cursor at the end of what is displayed. You will see there's an extra space after that obstinate number. Delete that extra space and all will be well.

If you want a bit of automation to it here's one way of getting rid of that unwanted space in a new column - in the example below the unwanted space (in my case in D9 cell) is replaced with a zero which then makes the cell a proper number:

=REPLACE(D9,LEN(D9),1,0)

I had coppied content from a pdf file and pasted it into notepad and saved it as .txt file. there are some number which are -ve in the table. I couldnt find any means to convert these general text to numbers, I tried everything suggested.

I often have the problem Kobus just mentioned -- I don't know the format of a particular cell. Is there any way to display all the format information of a cell, so you can start figuring out how to use it? This is especially important when importing data, which often looks like a number, but isn't.

I do not know in what format these figures are but I need to convert them into Excel format:

8,160.00
65.00 Cr

@Sanjay
Simply remove the parenthesis either manually or with Find and Replace

How can i convert this which are as percentage but appears as text
(1.57%) and (-1.53%)
I tried the value and multiply but that didnt worked

When I copy transactions from my bank's web site to Excel, the copy paste special with multiply by 1 or add 0 doesn't work. There must be some hidden character that excel doesn't like. Does anyone know why?

But when I run a macro that strips spurious characters from the data items in the range, the text automatically converts to numbers and I don't need to run these text to number conversions.

Such a macro may be a quicker way than the techniques described here. It works quite nicely, especially if there is no real text in the range.

@AFS,
Try the suggested formula in the linked picture:
http://postimg.org/image/hhffd8lhd
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

Me telecom provider submits its bill and details of calls in excel format. The cost column contains "currency numbers" in format as £ space value e.g. £ 1.50

Excel does not recognise this as text nor as number and using any operand sum (or Sigma) add divide etc on this cell and all the cells returns either "value" error or zero

How to identify the cell format and or convert the format to the number

Peter that is an AWESOME trick.
This was exactly what I was looking for. It allows me to leave number as text (for formatting reasons) and I am still able to complete my math on a range.
(I kept trying to work with the value function on a range and was unsuccessful.)

Thank you for that wonderful bit of knowledge.

If there is a reason for the number to be stored as text, you could convert that text to a number with Value( ). For examples:
Value(A1) + A2

You can also use SUM as an Array formula to total formatted text. e.g.
=SUM(A1:A6+0) or =SUM(A1:A6*1) Both formulas entered Ctrl + Shft + Enter.

William: It doesn't work because changing the format of the cell doesn't cause Excel to reparse the contents of each cell. It is the reparsing that is necessary to do the actual conversion from text to a numeric value.

Owen: The TEXT function forces numeric values to their text equivalent. It is, indeed, the opposite of what is discussed in this tip.

Rudra: I covered that in the tip, in the second or third paragraph after Figure 2.

-Allen

Instead of typing 1 and copying it, try copying blank cell. Excel uses blank cell as 0.

With Regards
Rudra

I have the reverse problem to that of Wm. Larson. ie converting numbers to "real" text ie with a ' ^ or " prefix added to the number.
I find I have to use a formula to do this.
eg =TEXT(F8064, "0")

I would think that selecting the cells and setting the format to number should do so. Can anyone explain to me why Microsoft has decided to make this not work? I.e. is this a feature or a bug?