Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Converting Number In Text To Actual Numbers

I've been given a spreadsheet which comes out of a time tracking application. The spreadsheet list projects and resources(people), and the time expected and acutally spent. Currently a lot of manual changes are being done once this report is generated. The report is always in the same format but because of the number of resources the rows specific data appears will vary from month to month.

Example:
Row 1 = Project Name
Row 2 = Resource Name 1 (this row also contains the hours for resource1)
Row 3 = Resource Name 2 (this row also contains the hours for resource2)
Row 4 = Totals (totals up the number of resource hours for each week)

My problem is that the values reported are in a Text Format. I've created a worksheet thats grabbing the data from the original report, but when I attempt to sum the totals for each week to get the totals for the month I get 0 because the values on the main worksheet are text, also I want the user to be able to paste in the next months report without having to convert manually (hoping I can do a conversion formula on my raw data worksheet). I'm using a lot of IF formulas to pull specific data from the report, and I've pulled over my project names and the totals per week, but now due to the conversion problem I can't sum for the month.

I've used a similar formula in the past but its for an opposite situation (number to text) =TEXT(A1,"0000000000"), is there a formula that converts it to numeric form? sorry I couldn't post an example.

Thanks in advance for any help.

Special Note: I hope the Title was accurately written for this post, tried to make it descriptive enough, thanks again for your help.


Post your answer or comment

comments powered by Disqus
Hi,

This is my first post. I need to know if there is a way to automatically have "text numbers" converted to actual numbers? Data is being copied from reports into the sheet i am using by a number of different people and most of the time it is text numbers being pasted. The range for the data being entered is C3:D102. Is it possible to enter a code that would convert these to actual numbers as soon as they are pasted?

I have a list of number in text format I need to convert them to decimal number like:-

From 1440 to 1.440
From 6729 to 6.729
From 12185231 to 12,185.231

Thanks.

Hi,

I am back to doing my Excel projects, and just found out that I need to convert some text to a number. I was wondering if there is a way to do it in Excel. Like, for example, the date 3/14/2006 can be converted to 38790. Is there any function in Excel that can convert a word to a number?

Example would be (numbers are totally random)

md_bla_bla_02 would convert to something like 3452615261502

I hope someone can help me. Thank you in advance!

Nat

HI,

while using ms excel 2003 i am unable to convert any numeric number in text
fromat in another cell or worksheet. what i want to do, that can be explained
as follows: " in cell a1 i typed Rs. 1000" and i want this figure to
converted in cell c45 as " Rupees one thousnad". could you please help me out
on this and the same thing as vice versa.

thanks,
abhishek

For a weekly timesheet, I needed to calculate the difference between two times in column C, so I used the formula =TEXT(B2-A2, "h:mm") where A is the start time and B is the end time.
But when I tried to total at the bottom of C column, the answer was zero. What can I do to have it convert the 8 number stored as text to a number to add the numbers to total the work week hours?

I need to convert numbers as text to values that can be added for a long
column of entries. HELP

Please send me how to convert Rs. 1234.00 in Displays the number in text.
(One thousand two hundred thirty four INR in English text)

i getting in Baht in Thai Text but i need in English. How do i get it?

i am also using [=spellnumber(b14)] function but this function working only
one time for using.

Hi all, please help me out

on how can i match the values written in text with actual numbers
for e.g

i need to match the Result
A B =IF((VALUE(A1))=B18799,"T","F")
1) NULL F
2) 0.582753183 0.582753183 F
3) NULL F
4) 1 1 T
5) 2.35 2.35 T
6) -0.293523862 -0.293523862 F

As per the above mentioned e.g which is exact my prob
Col1(A) is having the values in TEXT and Col2(B) is retrieving values with Vlookup

i am not worry about the 1 and 3. But for the values in row 2,4,5 and 6 have same values in it, why is the result says F for ROW 2 and 6 as the same formula[=IF((VALUE(A1))=B18799,"T","F"))] is used under "Result"

Please let me know what else do i need to include in "IF" formula, i need to edit only IF formula. Appreciate if i do not add any new column.

Really in need ...

Thanks
GP

I have a question on converting number in text to time format of 24 hours.

The information is copied from another data file and gets pasted as a text
file of 4 digits. IE: 'XXXX

I tried the formula of =VALUE(LEFT(D2,2))&":"&(VALUE(RIGHT(D2,2)))
to get a time format but it is loosing #s when converting 0.

IE 0503 converted is 5:3
it misses the 2nd 0 Any suggestions to keep both 0 so it will come out
05:03???

any and all help would be appreciated.. Thanks

I have a column of numbers in text format. I need them in number format - IF
I use Format -> cells - > number cat. - it does not work

Any other suggestions?

Thanks
greg

I have a question on converting number in text to time format of 24 hours.

The information is copied from another data file and gets pasted as a text
file of 4 digits. IE: 'XXXX

I tried the formula of =VALUE(LEFT(D2,2))&":"&(VALUE(RIGHT(D2,2)))
to get a time format but it is loosing #s when converting 0.

IE 0503 converted is 5:3
it misses the 2nd 0 Any suggestions to keep both 0 so it will come out
05:03???

any and all help would be appreciated.. Thanks

Afternoon, i need help. Apologies if this may be in the wrong forum, its my first time on here.

I am creating a review sheet/skill matrix using excel and need to be able to convert a particular selection to a number in the adjacent cell to work out the users efficiency (long story that i wont bore you with)

So basically i have a question/statement in cell A and a drop down answer menu in cell B containing up to 5 possible answers (very poor, poor, ok, good, very good). In cell C i would like to insert a formula that allocates a number dependant on the selection in cell B. So if the user selects very poor in cell B cell C states 1, if cell B has poor selected then cell C states 2 etc etc

Anyone have any ideas if this can be done and how i do it.

Thanks
Shez

Need to change the number in Text Box in Excel. For example; (Text Box 209).
I want to change that number to (Text Box 1). In fact I would like to
sequence the Text Box numbers form 1 to 160. Can you help?

Please Help!!!
I am trying to have excel find the highest x number in a group of
numbers and sum them up.
Example:

Week 3
156
222
172
185
158
178
166
185
133

I need the Top 3 numbers summed up. (222,185,185) = 592

Changes Weekly:

Week 4
156
222
172
185
158
178
166
185
133
195
178
222

I need the top 4 numbers summed up. (222,222,195,185) = 824

This will go on for 44 weeks.

I need excel to pick the highest 3 numbers and sum them up. I cannot
use the large function because it will pick only 1 number and I need
the x number cannot change on it's own. I need it to pick 222,185,185
and sum them up I have a database with over 150+ columns which I will
have to sort using a macro and then apply the following formula. This
range will increase weekly and for every 3 numbers added, I will need
to pick 1 more of the highest numbers and sum them up. I thought I had
it by finding the x largest number example 4th(using x large formula)
highest number (185) and making a formula with
=SUM(OFFSET(C9,MATCH(C143,C9:C140,0),0,COUNTIF(C9: C140,">"&C143)))
though, I found that if there is more than one number of the same
number, it will error. if i change the > to >=, it will pick too many
numbers. (C143 has the formula to find the x highest number(determined
in another cell).
I hope you understand what I am trying to do. It is very complicated
to me and I am now officially frustrated. Over 10 hours on this
(writing macros for sorting, etc.) Please help if you can. Thank You!

I want to replace all the cells that have no text or number in them to the
number Zero. What do I do?

I'm trying to count the occurrence of a number in a column as it corresponds to another value in another column. In column A there is a list of restaurant item numbers, some of which reoccur, I want to know how many times the number 4, in column C, occurs in relation to each item number. for instance, Item#100401 has 28 occurrences of the number "4". Thanks again for the help!

Please Help!!!
I am trying to have excel find the highest x number in a group of
numbers and sum them up.
Example:

Week 3
156
222
172
185
158
178
166
185
133

I need the Top 3 numbers summed up. (222,185,185) = 592

Changes Weekly:

Week 4
156
222
172
185
158
178
166
185
133
195
178
222

I need the top 4 numbers summed up. (222,222,195,185) = 824

This will go on for 44 weeks.

I need excel to pick the highest 3 numbers and sum them up. I cannot
use the large function because it will pick only 1 number and I need
the x number cannot change on it's own. I need it to pick 222,185,185
and sum them up I have a database with over 150+ columns which I will
have to sort using a macro and then apply the following formula. This
range will increase weekly and for every 3 numbers added, I will need
to pick 1 more of the highest numbers and sum them up. I thought I had
it by finding the x largest number example 4th(using x large formula)
highest number (185) and making a formula with
=SUM(OFFSET(C9,MATCH(C143,C9:C140,0),0,COUNTIF(C9:C140,">"&C143)))
though, I found that if there is more than one number of the same
number, it will error. if i change the > to >=, it will pick too many
numbers. (C143 has the formula to find the x highest number(determined
in another cell).
I hope you understand what I am trying to do. It is very complicated
to me and I am now officially frustrated. Over 10 hours on this
(writing macros for sorting, etc.) Please help if you can. Thank You!

=BAHTTEXT(A2) Displays the number in text.

THIS FUNCTION SHOWS IN THAI LANGUAGE.

IS THERE ANY OTHER FUNCTION FOR ENGLISH LANGUAGE?

Please tell me how I can put a number in text formate.
eg. 150 - One hundred and fifty

regards
Shantha

I'm desiging a spreadsheet for measurements and i want to number cells according to the number of samples i'll be measuring. For example, if my sample size is 20, i want to be able to input "20" into a cell and have 20 rows in a column numbered 1-20.

Hello everyone, I am new to this forum and have been using excel for about 3 years. I have learned alot but still can not figure out how to make numbers add up in one box like a running total. Meaning, If I have a list and in the list is one cell for each item say like cell a1 is for a can of coke and cell a2 is for a car tire and so on,and i want to keep a running total by adding a number to cell a1 or a2 like say "3 i.e. 3 tires or cans of coke" and later that day i come back and need to add 2 more ......so i want to be able to just click on the cell a1 or a2 and enter the number 2 and the cell would add the number in it to the number im adding to it to show a result of 5 tires or whatever. I am sorry If i am not asking the question in an understandable manor but its the best way I can think to ask it.lol anyways, any help would be appreciated, Thanks in advance.

James

Hello,

I have highlighted rows, (bold) when i try to convert them in text to columns the highlighted rows becomes normal.
How to keep this highlighted rows (bold) intact on split.

e.g.
HIGHLIGHTED(bold)__ After text to columns

'A'_________________ 'A'______ 'B'
Custodian : 87383 Custodian|87383 (removes the bold part in this 'B' column)
some data
some data
Custodian : 98599 Custodian |98599

I want to use 'homemade' cheque printing utiity for cheque printing. I need
to convert amount in figures to amount in words. Is thare any ready made
utility / macro / function / add on available ?
--
Shrikant

hi
pls help me with the formula to convert amount in cell to words like it shows sometimes on commercial invoices
thanks
raj


No luck finding an answer? You could always try Google.