# Underline in a formula

#### angusl

##### Board Regular
I want to concatenate a string and underline part of the string. Something like this:

12 2" Rollers

with the underline starting about three spaces left of the 12 and running three spaces to the right of it. 12 and 2" rollers are pulled from other cells.

Any ideas?

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Andrew Poulsom

##### MrExcel MVP
No can do in the result of a formula.

#### WJReid

##### Active Member
Hi Angusl,

If your 2" was in cell A1 and Rollers in cell B1, then in the cell that you are concatenating the text in, say cell C1, you would have =" "&A1&" "&B1&" ". That is 3 spaces in the inverted commas at the beginning and end. Then format the result cell as underligned. This should do the trick.

Regards,

Bill

#### WJReid

##### Active Member
Hi Angusl,

That should have of course been 12 in cell A1 and 2"Rollers in cell B1.

Regards,

Bill

#### angusl

##### Board Regular
Thanks. I was beginning to think in that direction. The quantity (2) may be one to three characters long, though. I think this is going to require a macro.

#### WJReid

##### Active Member
Hi Angusl,

If you don't want 3 spaces at the beginning and end of every result, you can still do it without code if you vary the number of spaces depending on the length of the number in cell A1, as in the example below
=IF(LEN(A1)=1," "&A1&" "&B1&" ",IF(LEN(A1)=2," "&A1&" "&B1&" "," "&A1&" "&B1&" "))

Regards,

Bill

Replies
0
Views
173
Replies
5
Views
112
Replies
7
Views
98
Replies
3
Views
40
Replies
7
Views
157

1,181,535
Messages
5,930,464
Members
436,741
Latest member
buckeyerich

### 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