# Numeric+Text Column

#### ainz002

##### New Member
Hi there! I would like to ask if someone can help me witht this excel case:
 A1 B1 C1 1.4um Thick 1.2um Thick Test 1

<tbody>
</tbody>

I want t compare the numbers in A1 and B1 so that the text in C1 will be highlighted.
Example: If A1>B1 (1.4>1.2), then 'Test 1' in C1 will have red text.

Is there a way to do this without having to add another column to separate the number from the text?

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
hi aniz! you can use left formula to extract numeric numbers only. as if(left(A1,3)>left(B1,3) then Range(C1).Font.Bold

You should use conditional formatting to create the red font.
Use this formula in the conditional formatting
(--LEFT(A2,FIND("um",A2)-1))>(--LEFT(B2,FIND("um",B2)-1))
This will work even if the numbers are not all the same number of digits, but it does require all numbers to be followed by "um"

Try conditional formatting, select C1 then Home tab > Conditional formatting > Highlight cell rules > More rules > New Formatting rules > Use formula to determine which cell to format > edit the rule description > =--LEFT(A1,3)>--LEFT(B1,3) > Format > Color > Red OK out.

Regards,
Howard

select cell A3 then formula in conditional formatting

=LEFT(\$A\$1,3)>LEFT(\$B\$1,3)

Thanks everyone for your quick response. It saved me many hours of trying to browse the net for the solution. haha! Btw, I used L. Howard's suggestion.

Try conditional formatting, select C1 then Home tab > Conditional formatting > Highlight cell rules > More rules > New Formatting rules > Use formula to determine which cell to format > edit the rule description > =--LEFT(A1,3)>--LEFT(B1,3) > Format > Color > Red OK out.

Regards,
Howard

Thanks for the response and instruction L. Howard. It's not really complicated as I thought it would be.

You are welcome.

Thanks for the feed back.

Regards,
Howard

Hi! I have a follow-question. If there is at least one 'red text' in Column C, I would like to make overall assessment in Column C like ex. C30: "Experiment Failed".
This is another conditional formatting but i dont know what is the formula. Thanks once again!

In the vb editor look at the top left of the page between View and Format. Click on INSERT then Module. When you get the module paste this in it.
On the worksheet in a cell enter , "=countcolor(\$C\$1:\$C\$25,255)" (no quotes) and adjust the range to cover your red font anticipated cells.
To check on your experiment press F9. The code will fire with each calculation on the sheet which may be a bother but will only produce the message if you have red font in the range in the formula.

Code:
``````Option Explicit
Function countcolor(rng As Range, colorindex As Integer)
Dim count As Integer
count = 0
Dim rw As Range
For Each rw In rng
If rw.Font.Color = colorindex Then
count = count + 1
End If
Next rw
countcolor = count
If countcolor > 0 Then
End If
End Function``````

'On the sheet use =countcolor(\$C\$1:\$C\$25,255)

Regards,
Howard

Replies
2
Views
237
Replies
1
Views
404
Replies
2
Views
151
Replies
21
Views
773
Replies
0
Views
156

1,196,049
Messages
6,013,106
Members
441,748
Latest member
MrBigglesworth

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back