Formatting & Checking Text in a cell to ensure a minimum length of 30 characters

[robertguy]

Hi,

Can anyone help me please as I'm trying to join three cells of tex
together A1 + A2 + A3 with the output in cell A4

e.g. "Help" & "Me" & "Please" = Help Me Please (in cell 4 whic
works fine using the &)

However, for formatting purposes I need to ensure that the text in A
has a minimum of 30 characters so "Help Me Please" would have to b
shown as "Help Me Please plus 15 spaces then an "!” mark

e.g. "Help Me Please !"

How do I check the text in A4 is less than 30 characters adding any a
necessary to add up to 29 then add the “!” mark in the 30th position ?

Any suggestions would be greatly welcomed



Ro

 

[Frank Kabel]

Hi Rob
one way (if you only concatenate A1-A3 without adding additional spaces
between those cells)
=A1 & A2 & A3 & REPT(" ",MAX(0,29-LEN(A1 & A2 & A3))) &
IF(LEN(A1&A2&A3)<30,"!","")

Frank

 

[robertguy]

Franks

Many thanks !!

It works a treat


Thanks again

Rob

;

 

[Frank Kabel]

Hi Rob
thanks for the thanks
Frank