How to add 'filler' characters up to N limit to a variable length string using Excel / Sheets formulae?

0

I have a list of strings (names / description text) that I need to add 'filler' characters to.

The maximum string length is 32 - for any text under that (in characters) I need to add special filler characters - up to that limit.

EG:

Original String - 30char - ReallyLong String Name Example

Revised String - 32char - ReallyLong String Name Example**

What is the best way to do this either with excel / sheets on it's own OR with a formula?

Thank you for your time!

user11788026

Posted 2019-07-15T18:13:21.907

Reputation: 1

Answers

3

Add the characters then cut to 32 length.

=LEFT(CONCAT(A1,"********************************"),32)

Brian

Posted 2019-07-15T18:13:21.907

Reputation: 681

2Nice one @Brian, to make it even tidier: =LEFT(A1&REPT("*",32),32), you can leave out CONCAT as it serves no purpose in here, plus it's a function only available from office 365 insiders and office 2019 if I'm not mistaken. :) – JvdV – 2019-07-15T20:08:21.663

1@JvdV Cool Rept() function JvdV. Then I can satisfy my OCD with =IF(LEN(A1)<32,A1&REPT("*",32-LEN(A1)),A1) Did not realize concat() might be limited. Hopefully at least the fully spelled out concatenate() is available to all users. – Brian – 2019-07-15T20:49:51.013

It certainly is available, but why a check for Len? It's irrelevant – JvdV – 2019-07-15T20:57:20.373

Asked: 2019-07-15T18:13:21.907

Viewed: 37 times

Active: 2019-07-15T18:26:20.830