I need to create a formula using alpha numeric charetcers...i keep getting #value...what does this mean?
my numbers look like this LLLL##########
2007-12-20 01:13:52 · 5 answers · asked by karrybear_2000 1 in Computers & Internet ➔ Software
Okay...I have a list of numbers always LLLL to start followed by at 7 or more numbers. in sheet1.
I want to copy and paste data from a website on sheet2
I want the formula to look up my LLLL#### on sheet1, find it on sheet2 and enter a code that was also pasted from the website that will be in the column beside sheet2 LLLL######.
It's sounds confusing to me, i wish i could paste my spreadsheet...
2007-12-20 16:35:20 · update #1
If I'm understanding you right, you're probably using arithmetic operators (+, -, *, /) on cells containing text. That won't work.
If the cells containing LLLL########## always have "LLLL" followed by 10 numbers, then create a column next to the one where you have LLLL##########, and in the first cell, use the following formula:
(note I'm assuming the column with the LLLL########## is A and your first row is 1)
=VALUE(RIGHT(A1,10))
Then fill down.
At that point you should be able to do calculations on the values you got -- and then you could hide the column you just created so that it doesn't show on your table.
Edit: the formula above pulls the last 10 characters from the A1 cell. For that reason, it will only work if your data is always like LLLL followed by 10 characters/numbers, no more, no less. That's what the RIGHT part does.
RIGHT being a text formula, the VALUE part of the formula converts it to numbers.
Also, read this to understand the #VALUE! error:
http://www.updatexp.com/excel-value-error.html
2007-12-20 01:23:33 · answer #1 · answered by Anonymous · 0⤊ 0⤋
If the first 4 characters are always LLLL then go to Format --> cell --> custom and in the type box type LLLL# # # # # # # # # #.
If the first characters are Alpha but may not always be four of them or may not be LLLL, then type the alpha characters in cell A1 and the numeric characters in B1 and in C1 input the formula ="A1"&B1.
Does this help?
2007-12-20 19:51:28 · answer #2 · answered by HazMat 4 · 0⤊ 0⤋
si=so. If you type incorrect data/format in the formula, it will give you the #value error message.
add some more information, so we can help.
2007-12-20 12:12:01 · answer #3 · answered by clinky 3 · 0⤊ 0⤋
What do you want to do with the "numbers"?
2007-12-20 09:21:43 · answer #4 · answered by Anonymous · 0⤊ 0⤋
What's your formula?
2007-12-20 09:21:49 · answer #5 · answered by thesunwasshiningonthesea 5 · 0⤊ 0⤋