Underline in a formula

angusl

Board Regular
Joined
Apr 15, 2002
Messages
55
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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

WJReid

Active Member
Joined
Jul 26, 2002
Messages
317
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
Joined
Jul 26, 2002
Messages
317
Hi Angusl,

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

Regards,

Bill
 

angusl

Board Regular
Joined
Apr 15, 2002
Messages
55
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
Joined
Jul 26, 2002
Messages
317
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
 

Forum statistics

Threads
1,144,770
Messages
5,726,186
Members
422,661
Latest member
foxleinlady

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top