A customer sent me a spreadsheet containing data that had been exported from another system asked me to convert the “values” in column A (which were text) to numeric values.
**Download the sample spreadsheet here**
The first thing that I noticed was that there was at least one leading space in each cell, in other words, each cell entry begins with one or more space characters. To remove spaces at the beginning (and end) of a cell entry, use the TRIM function.
B2 contains the function =TRIM(A2)
The result is that B2 contains the same as A2 but with the spaces removed:
I had to ask my customer for clarification – should the value in A2 be one-hundred-and-twenty or one-hundred-and-twenty-million or one-hundred-and-twenty-thousand with 3 decimal places, or something else?
The answer came back that A2 should contain one-hundred-and-twenty-thousand with 3 decimal places. As my customer is UK-based, the comma should be used to separate the thousands and the full-stop/period should be used to separate the decimal – i.e. 120,000.000
The next step was to replace the full-stop with a comma and the comma with a full-stop. I used the SUBSTITUTE function but did it in 2 steps.
C2 contains the function =SUBSTITUTE(B2,”.”,”,”)
This replaces the full stop in B2 with a comma
Now I need to replace the comma in C2 with a full stop, but with the original comma still there and an extra comma now added (thanks to the previous step), C2 contains 2 commas. I only want to replace the 2nd occurrence. Again I used the SUBSTITUTE function but with an extra argument:
D2 contains the function =SUBSTITUTE(C2,”,”,”.”,2)
This replaces the SECOND occurrence of the comma in C2 with a full stop
Finally, in E2, I need to convert the textual value into a numeric value. There are 2 ways to do this:
The VALUE function converts a text-based number into a real number. To do this, I would enter =VALUE(D2) into E2. However, I would have to manually format the cell to display commas and 3 decimal places.
I chose instead to use the FIXED function which displays a numeric value with a fixed number of decimal places, optionally adds commas and, if appropriate, converts a text-based number into a real number
E2 contains the function =FIXED(D2,3,FALSE)
This instructs Excel to display the value of D2 (which it converts to a number) with 3 decimal places and to include commas (TRUE means prevent commas, FALSE means do not prevent commas)
sir,
1,05,434.00
1,98,08,149.00
2,11,461.00
72,874.00
how to convert this text number into number so that total i can get.
Hi
It depends on what the original number should be. For example:
1,05,434 – should this be 105,434?
1,98,08,149 – should this be 19,808,149?
What I have done is made a guess as to what outcome you want. If this is correct then in A1, type 1,05,434.00 and in B1 type: =VALUE(TRIM(SUBSTITUTE(A1,”,”,””)))
Then put the other numbers down column A and copy the formula
The formula removes the commas, replacing them with nothing (SUBSTITUTE function. It removes any spaces (TRIM) and converts the result into a numerical value (VALUE)
Let me know if this is what you want!
-Mike-
okay I have a text number of say 3000 and I want to insert a decimal three numbers over so it reads only 3. another example if I have a 52674 I need it to read 52.674. how do I convert these numbers to insert a decimal?
I’m assuming that the . after the 3 is a decimal point and not the end of the sentence? If so…assuming that the “text number” is in A1, use this formula: =VALUE(A1)/1000 & “.”
This converts what is in A1 to a numeric value, divides that by 1000 and sticks a decimal point at the end. Unfortunately this converts the result back to text. (the & “.” causes this) but if you need to use this result (i.e. the 3.) in another formula, just use =VALUE in the other formula to convert to a value.
For your other example, use =VALUE(A1)/1000
What if i need to convert text number into 2 decimal. Tried the method above but i got “00” appended to my number. Example below.
5000 becomes 50.00
1234 becomes 12.34
TQIA!
I’ve just tried it with the example spreadsheet I used in the original tutorial by changing the last function in column E to =FIXED(D2,2,FALSE)
Need help to omit all special characters (eg: -,!,@,#) in between alphanumeric cell. What’s the best way to get rid of them and to retain the alphanumeric only. TQ in advance.
I have downloaded certain numbers and I see ” 1000″ but TRIM and SUBSTITUTE functions do not work. Only option is press F2 and then home and remove all spacing. Bit difficult for doing the same for 10,000 line items.
@Noman – Try the CLEAN function – e.g. =CLEAN(A1). I think what you have are non-printing characters, not blanks
How can I convert a number “700802587.” from Text to remain with 700802587.
In A1 enter: 700802587.
In B1 enter: =VALUE(A1) & “.”
B1 will be treated as a numeric value
HI,
How can I write text format like e.g. OK to number (1)? In a column.
Kindly suggest how to convert all text strings to numbers in a column in Excel sheet? Example:
2,66,020
2,66,020
2,24,650
70,170
91,250
2,66,810
2,66,810
91,250
2,66,810
2,64,840
2,67,720
17,150
2,67,720
2,360
Thanks. RAKESH
Hi Rakesh. Suppose the text is in column A (let’s use 2,66,020 in A1 as an example) and you want the converted number to be in column B, type the following formula…
=VALUE(SUBSTITUTE(A1,”,”,””))
This replaces the commas in the text string with nothing and then converts the result to a numeric value