0

Trying to set a number of characters in a cell to 40. Converting to a CSV then to Text format that will have the 40 characters with "," delimiters in it. I.E.:

10,HELLO , 10

HELLO being the 40 Characters total 5 with the "HELLO" then 35 'spaces' after.

Can this be done? I have tried Data Validation equal to 40 and does not work.

T-Diddy
  • 125
  • 1
  • 12

1 Answers1

1

Use this formula to create a new column for your text field. This will pad the value with spaces up to 40, as well as trim to 40 if the value exceeds it.

    =LEFT(B2 & REPT(" ",40),40)

enter image description here

Isolated
  • 5,169
  • 1
  • 6
  • 18
  • This is perfect, thank you. Happen to know how to put leading spaces BEFORE any characters? – T-Diddy Aug 18 '20 at 18:57
  • 1
    Well, if you want to add perhaps 5 spaces, then you could do this: =" "&A2 where you space the number of spaces between quotes. – Isolated Aug 18 '20 at 19:56
  • 1
    If you want to do everything at once (value is prefixed with 5 spaces and suffixed with enough spaces to make 40 wide), then it may look like this: =LEFT(" "&A2 & REPT(" ", 40), 40) – Isolated Aug 18 '20 at 19:56