Separating numbers and text and funky rounding

[waterexcels]

Morning all,

I've been racking my brain with these for a while and I am getting frustrated. I have a column of numbers, some with letters, and need to split the value based on the letter. Example:

A1
4
4 U
4 I

If the cell has just a number, do nothing. If it had a U, remove the U and divide the number by 2. if it has an I, leave the cell blank. Now I've tried every combination of IF, FIND, LEFT, ISNUMBER I can think of and I am only able to do 2 of the 3 test. I need to do all 3

And if there is a function to do scientific rounding with significant figures, I need a script to do that as well. I seem to remember Excel had a function for that. IE: rounding to even numbers. I dont know how to explain scientific rounding because it confuses me still to this day

Thank you in advance

-D

[hemesh]

can you upload a sample book with desired results.

assuming your data is in A1 to A10
in b1 try this =IF(ISNUMBER(A1),A1,IFERROR(IF(SEARCH("U",A1)>=1,ROUND(SUBSTITUTE(A1,"U","")/2,0),""),""))

[waterexcels]

Hey thanks for the reply. i don't think I can upload this data.

I also should have mention the 2 functions need to be separated. They are for 2 different computations

[hemesh]

you can upload atleast 10-15 rows of look alike data with expected results to work with !

[AZ-XL]

Hi
=IF(ISNUMBER(A1),A1,
IF(ISERROR(SEARCH("u",A1)),"",ROUND(--SUBSTITUTE(A1,"U","")/2,0)))

[AliGW]

I also should have mention the 2 functions need to be separated. They are for 2 different computations

You mentioned three functions in your opening post, so which two do you need to be able to do and which one have you already worked out (and how are you doing it)? What do you mean by 'scientific' rounding and how does it differ from mathematical rounding (i.e. .5 or more rounds up, below that rounds down)?

[waterexcels]

You mentioned three functions in your opening post, so which two do you need to be able to do and which one have you already worked out (and how are you doing it)? What do you mean by 'scientific' rounding and how does it differ from mathematical rounding (i.e. .5 or more rounds up, below that rounds down)?

The rounding function is separate from the function that deals with the the column A1

Scientific rounding is stupid. I cant really explain it in an easy way without confusing both of us. But I do think there is a built in function in Excel, I just cant remember it

[waterexcels]

Here is how scientific rounding works:

If the last digit after the decimal is a 5, you want to round the digit before it to the closest even number. If the last digit is any other number, you round as normal, to sig figs.

Assuming 2 sig figs:

2.35 would be 2.4
2.25 would be 2.2
2.45 would be 2.4
2.34 would be 2.3
2.37 would be 2.4
0.05 would be 0.0
0.155 would be 0.2

Confused?

[waterexcels]

Hi
=IF(ISNUMBER(A1),A1,
IF(ISERROR(SEARCH("u",A1)),"",ROUND(--SUBSTITUTE(A1,"U","")/2,0)))

Hey AZ-XL, that worked! I removed the ROUND function, and it did exactly what i needed. Thank you!

I appreciate everyone's help. I will now try to work on the rounding function. I have an idea...