Underline in a formula

[angusl]

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?

 

[Andrew Poulsom]

No can do in the result of a formula.

 

[WJReid]

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]

Hi Angusl,

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

Regards,

Bill

 

[angusl]

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]

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