Convert Alpha-numeric To Numeric |
Convert Alpha-numeric To Numeric - Excel |
|
Hi,
Does anyone know of a way to convert alpha-numeric text to numeric text? E.g. I want to be able to convert 2a (which is in one column) to the value 17 (in another column)
Many thanks,
Michaelmac
Does anyone know of a way to convert alpha-numeric text to numeric text? E.g. I want to be able to convert 2a (which is in one column) to the value 17 (in another column)
Many thanks,
Michaelmac
Excel VBA Course - From Beginner to Expert
200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
(80% Discount Ends Soon!)
Similar Topics
i've been trying to figure this out on my own but seem to be hitting road blocks.
i have a column of numbers that we extracted from a database as whole numbers. for instance, 30 should be .30 and 100 should be 1.00. when i try to increase or decrease the decimal point it doesn't allow me to convert it to those positions.
would any one have a tip of how i can easily convert this? there are over 2000 entries in my excel spreadsheet so to do it manually would take a lot of time/effort.
thank you!
i have a column of numbers that we extracted from a database as whole numbers. for instance, 30 should be .30 and 100 should be 1.00. when i try to increase or decrease the decimal point it doesn't allow me to convert it to those positions.
would any one have a tip of how i can easily convert this? there are over 2000 entries in my excel spreadsheet so to do it manually would take a lot of time/effort.
thank you!
Hi guys,
would really appreciate anyones help with this.
I have a column full of text-formatted fractions...
4/5
6/4
3/1
2/5
4/5
etc, etc.....the column is very long!!
I need to convert these to actual fractions...ones I can add to equations, allowing me to multiply and add.
If I highlight the column and goto "format Cells" and change to fraction, the values still stay in the left of the cell.....its only when I manually click on each individual cell, then tick the little green tick, does the cell validate, and the value in the cell moves to the right (and is therefore a proper value).....problem is I have to keep doing this for each individual cell.....and I have 70000+ cells in this column!
Any suggestions on how I could speed this up?
thanks
would really appreciate anyones help with this.
I have a column full of text-formatted fractions...
4/5
6/4
3/1
2/5
4/5
etc, etc.....the column is very long!!
I need to convert these to actual fractions...ones I can add to equations, allowing me to multiply and add.
If I highlight the column and goto "format Cells" and change to fraction, the values still stay in the left of the cell.....its only when I manually click on each individual cell, then tick the little green tick, does the cell validate, and the value in the cell moves to the right (and is therefore a proper value).....problem is I have to keep doing this for each individual cell.....and I have 70000+ cells in this column!
Any suggestions on how I could speed this up?
thanks
I am looking for assistance in having one cell in a text format equals another cell that contains a time value in hh:mm format.
For example: Cell A1 has a time format (hh:mm) value of 04:00; which is the Start Time. I would like cell D1 to have a text format value of "04:00" (result is dependant upon what is entered in A1). I would duplicate the same formulas to reflect Stop Times in other cells.
My final result is to have another cell (F1) use the Concatenate formula to have the Start and Stop time shown in one cell as "04:00 - 12:30". The times would change based on the Time formated values entered into the Start and Stop time cells.
I have researched this in the board and found many excellent ways to do the opposite, but not convert Time format to Text format. Any assistance is greatly appreciated.
-Shane
For example: Cell A1 has a time format (hh:mm) value of 04:00; which is the Start Time. I would like cell D1 to have a text format value of "04:00" (result is dependant upon what is entered in A1). I would duplicate the same formulas to reflect Stop Times in other cells.
My final result is to have another cell (F1) use the Concatenate formula to have the Start and Stop time shown in one cell as "04:00 - 12:30". The times would change based on the Time formated values entered into the Start and Stop time cells.
I have researched this in the board and found many excellent ways to do the opposite, but not convert Time format to Text format. Any assistance is greatly appreciated.
-Shane
Hello All
I wonder if you could help with this one, I have a list of data like this (no spaces or other characters)
JN551122B
What I need to do is split each of the characters in to its own cell
if JN551122B was in cell A1 I'd want to return:
B2 C2 D2 E2 F2 G2 H2 I2 J2
J N 5 5 1 1 2 2 B
The nearest I have got is the right function, but it brings over the preceeding characters, and I need each character in its own cell. Hopefully I could copy/paste a formula and change the reference for each cell value, i.e. 1 would return J, 2 = N, 3 = 5, 4 = 5 etc.
Thanks for your help!
I wonder if you could help with this one, I have a list of data like this (no spaces or other characters)
JN551122B
What I need to do is split each of the characters in to its own cell
if JN551122B was in cell A1 I'd want to return:
B2 C2 D2 E2 F2 G2 H2 I2 J2
J N 5 5 1 1 2 2 B
The nearest I have got is the right function, but it brings over the preceeding characters, and I need each character in its own cell. Hopefully I could copy/paste a formula and change the reference for each cell value, i.e. 1 would return J, 2 = N, 3 = 5, 4 = 5 etc.
Thanks for your help!
In excel, I tried to convert numbers to number format, using
format/cells/number from the category list. But it won't take, and stays
text-like. Any ideas?
format/cells/number from the category list. But it won't take, and stays
text-like. Any ideas?
Hello,
My Cell C3 is a numeric value.....I have set conditional formatting to
make the text red and bold when the number is equal or less than 10,000
is there a way I can make cell E3 display a message when the C3
condition is true?? or if not a message is there a way to make a
message box pop up when my C3 condition of less then or equal to 10,000
is true?
how would I do something like this?
My Cell C3 is a numeric value.....I have set conditional formatting to
make the text red and bold when the number is equal or less than 10,000
is there a way I can make cell E3 display a message when the C3
condition is true?? or if not a message is there a way to make a
message box pop up when my C3 condition of less then or equal to 10,000
is true?
how would I do something like this?
Hi,
I made a simple worksheet that we have been manually entering the time for employees, but there are too many errors (even with simple math). Can someone help me convert the time of ex: written 8-4:30 or 10-3 (meaning 8:00am to 4:30pm) where you have the total weekly hours? Right now I have a column for overtime. Is there a way to automatically calculate the over time also? The work day is 8-4:30 with a half hour lunch (lunch is not calculated into the hours, so you minus a half hour). 8-4:30 equals 8 hours. So, if a person works 8am to 6:30pm, how can I set it up where in the first row of time, I will type in 8-6:30pm. In the totals column to the far right, it will display 8 hours. In the (O.T.) column, it will display 2 hours.
This way, when I call in my payroll, I will have the total "regular" hours in one row and underneath, I will have the total overtime hours.
Or... does anyone have a better solution to keeping track of their hours?
Thanks!
Marty
I made a simple worksheet that we have been manually entering the time for employees, but there are too many errors (even with simple math). Can someone help me convert the time of ex: written 8-4:30 or 10-3 (meaning 8:00am to 4:30pm) where you have the total weekly hours? Right now I have a column for overtime. Is there a way to automatically calculate the over time also? The work day is 8-4:30 with a half hour lunch (lunch is not calculated into the hours, so you minus a half hour). 8-4:30 equals 8 hours. So, if a person works 8am to 6:30pm, how can I set it up where in the first row of time, I will type in 8-6:30pm. In the totals column to the far right, it will display 8 hours. In the (O.T.) column, it will display 2 hours.
This way, when I call in my payroll, I will have the total "regular" hours in one row and underneath, I will have the total overtime hours.
Or... does anyone have a better solution to keeping track of their hours?
Thanks!
Marty
I was able to convert start date and and end date to the number of days
inbetween the two dates. Does anyone know how to then convert the number of
days into months?
inbetween the two dates. Does anyone know how to then convert the number of
days into months?
I have two columns in my spreadsheet. Column A has no blank fields, Column B has some blank fields. I would like to show the text from Column A in the same row of Column B only when Column B is blank.
So, basically I'm after: If column B has text, do nothing. If column B is blank, then list text from Column A.
Right now I've created Column C with this formula =IF(ISBLANK(B1), A1. That's working to get the text from A1 when B1 is blank. What can I do/add to get it pull the text from B1 if there is text there? Or...is there a better approach altogether?
So, basically I'm after: If column B has text, do nothing. If column B is blank, then list text from Column A.
Right now I've created Column C with this formula =IF(ISBLANK(B1), A1. That's working to get the text from A1 when B1 is blank. What can I do/add to get it pull the text from B1 if there is text there? Or...is there a better approach altogether?
My balance sheet is prepared in rupees.
I want to show all figures in Lacs like value 235600 show 23.56
In previous year i divided all values one by one by inputting /100000 but its so tedious.
Pl help.
I want to show all figures in Lacs like value 235600 show 23.56
In previous year i divided all values one by one by inputting /100000 but its so tedious.
Pl help.
Hi there,
How i can convert Hijra (Islamic) Dates into Gregorian?
a formula or VB
any help
How i can convert Hijra (Islamic) Dates into Gregorian?
a formula or VB
any help
I've been poking around trying to figure this out but can't. The time of 9 hours, 15 minutes is being returned from a calculation but I want the cell to display the 9.25 hours instead. This seems like something simple on the surface, but apparently it's not. Any help would be appreciated!
Hi,
I have the following code to put data from a VBA userform into Excel
Dim Sh As Worksheet
Dim Rng As Range
Set Sh = ActiveSheet
Set Rng = Sh.Range("A65536").End(xlUp).Offset(1, 0)
With Rng
.Cells(1, 1) = Surname.Text
.Cells(1, 2) = forename.Text
.Cells(1, 3) = datein.Text
.Cells(1, 4) = origin.Text
.Cells(1, 5) = Addressee.Value
.Cells(1, 6) = usual.Value
.Cells(1, 7) = dateto.Text
.Cells(1, 8) = permission.Value
.Cells(1, 9) = dateseen.Text
.Cells(1, 10) = requestview.Value
.Cells(1, 11) = Invoice.Value
.Cells(1, 12) = notes.Text
.Cells(1, 13) = datecompleted.Text
.Cells(1, 14) = holdsend.Value
.Cells(1, 15) = fee.Text
.Cells(1, 16) = notes2.Text
.Cells(1, 17) = dateseen.Text
.Cells(1, 18) = invoicesent.Text
.Cells(1, 19) = Paid.Text
.Cells(1, 20) = Complete.Value
End With
What I want to do next is click on the surname on the speadsheet and call up the userform with the fields complete for that person. I would also like to be able to edit/update the form and update the information on the speadsheet accordingly.
Any help would be greatly appreciated!
Many thanks!
I have the following code to put data from a VBA userform into Excel
Dim Sh As Worksheet
Dim Rng As Range
Set Sh = ActiveSheet
Set Rng = Sh.Range("A65536").End(xlUp).Offset(1, 0)
With Rng
.Cells(1, 1) = Surname.Text
.Cells(1, 2) = forename.Text
.Cells(1, 3) = datein.Text
.Cells(1, 4) = origin.Text
.Cells(1, 5) = Addressee.Value
.Cells(1, 6) = usual.Value
.Cells(1, 7) = dateto.Text
.Cells(1, 8) = permission.Value
.Cells(1, 9) = dateseen.Text
.Cells(1, 10) = requestview.Value
.Cells(1, 11) = Invoice.Value
.Cells(1, 12) = notes.Text
.Cells(1, 13) = datecompleted.Text
.Cells(1, 14) = holdsend.Value
.Cells(1, 15) = fee.Text
.Cells(1, 16) = notes2.Text
.Cells(1, 17) = dateseen.Text
.Cells(1, 18) = invoicesent.Text
.Cells(1, 19) = Paid.Text
.Cells(1, 20) = Complete.Value
End With
What I want to do next is click on the surname on the speadsheet and call up the userform with the fields complete for that person. I would also like to be able to edit/update the form and update the information on the speadsheet accordingly.
Any help would be greatly appreciated!
Many thanks!
I have data with time in standard fromat i.e. 3:00PM but I need it to be in
24 hour format i.e. 15:00. How can I do this?
24 hour format i.e. 15:00. How can I do this?
Hello,
I need to know how to auto-fill text based on text in another cell. For
example:
Every time I enter "CHS" in Column B, I want Charleston to auto-fill in
Column C.
And when I enter "SAN", in Column B, I want San Diego to auto-fill in Column
C.
How can I set up a list like this? Any ideas?
Thanks!
Jason
I need to know how to auto-fill text based on text in another cell. For
example:
Every time I enter "CHS" in Column B, I want Charleston to auto-fill in
Column C.
And when I enter "SAN", in Column B, I want San Diego to auto-fill in Column
C.
How can I set up a list like this? Any ideas?
Thanks!
Jason
I need to find data in two different formats within a column
Examples
Webb Christopher
Greer Nancy
I need to find cells with text that have more than one space between the first and last names; and cells with text that have only one space between the first and last names.
I am fairly new to excel. I can find the space after the first word but cannot tell it to find ones that say have a space then another string of text.
Thank you for your help!
Examples
Webb Christopher
Greer Nancy
I need to find cells with text that have more than one space between the first and last names; and cells with text that have only one space between the first and last names.
I am fairly new to excel. I can find the space after the first word but cannot tell it to find ones that say have a space then another string of text.
Thank you for your help!
Good afternoon,
Is there a way to enter a colon into a standard number to create a value that can be formatted into a 24 hour time value ?
eg a time is listed as 1345 with a general number format, and I want it returned as 13:45 witha custom format of hh:mm.
Other than creating a table and using a vlookup function, I am hoping there is a better way?
Darren
Is there a way to enter a colon into a standard number to create a value that can be formatted into a 24 hour time value ?
eg a time is listed as 1345 with a general number format, and I want it returned as 13:45 witha custom format of hh:mm.
Other than creating a table and using a vlookup function, I am hoping there is a better way?
Darren
I have a given length of steel bar...Example(550 feet)
I have various cut lengths that I can pick from...
14'-10".....13'- 9".. 12'-8"........(up to 6 different lengths)
I need to know the best combination of lengths to cut the long bar into to have minimal scrap left when we get to the end of the 550 feet.....
Cut as many of the longest cut lengths possiable.....Cut as few of the shortest cut lengths possiable
Due to the fact that I will have to convert this to a PLC application when completed, I want to find a mathmatical way to do this inside excel and not use the "solver"....Assuming that I/we can come up with an equation to do this .....
Any and all advice/help would be appricated.....
I have various cut lengths that I can pick from...
14'-10".....13'- 9".. 12'-8"........(up to 6 different lengths)
I need to know the best combination of lengths to cut the long bar into to have minimal scrap left when we get to the end of the 550 feet.....
Cut as many of the longest cut lengths possiable.....Cut as few of the shortest cut lengths possiable
Due to the fact that I will have to convert this to a PLC application when completed, I want to find a mathmatical way to do this inside excel and not use the "solver"....Assuming that I/we can come up with an equation to do this .....
Any and all advice/help would be appricated.....
I use Excel 2000 (9.0.2720)
In range A1:D10, I have entered the figures in actuals (e.g. 34532642.45,
78945624.89 etc.). How can I convert those figures in Lakhs / Thousands /
Crores? Before converting, Can Excel 2000 prompt me for conversion in Lacs /
Thousands / Crores?
In range A1:D10, I have entered the figures in actuals (e.g. 34532642.45,
78945624.89 etc.). How can I convert those figures in Lakhs / Thousands /
Crores? Before converting, Can Excel 2000 prompt me for conversion in Lacs /
Thousands / Crores?
Does anyone know if there is a customer format I can use to change the percent value of a cell to basis points? I tried creating my own, but was having difficulty. For example, if the cell value in A1 is 2%, I would like to have it say 200 basis points instead.
Thanks.
Thanks.