CODEWITHSUNDEEP
excelexcel-formula
Bob
Bob

Reputation: 477

How to add white spaces in a field of Excel which is equal to length of a longer word

I have a scenario where I want my Microsoft excel field to have the same length of the longest word in the column. Basically lets say if I have:

ACBBASDBBADSAD
BADFDFDDF

So here I want to have the second word with less characters to have white spaces at its end to match the length of the first word.

=&" " this definitely helps but I am unable to achieve the above scenario

Upvotes: 0

Views: 2793

Answers (3)

Jeff Kremer
Jeff Kremer

Reputation: 31

Replace a token / separator and pad with spaces for example a part number and revision.

Global Shop example 17 char Pat number and revision starts at position 18.

=IF(ISERR(INDEX(TEXTSPLIT(A1,"~"),1,2)), INDEX(TEXTSPLIT(A1,"~"),1,1),INDEX(TEXTSPLIT(A1,"~"),1,1)&REPT(" ",17-LEN(INDEX(TEXTSPLIT(A1,"~"),1,1)))&INDEX(TEXTSPLIT(A1,"~"),1,2))

Upvotes: 0

danieltakeshi
danieltakeshi

Reputation: 939

You can add this formula to count maximum characters and use on some cell, because you will need to press a command for it to work, so every cell can't contain this formula, let's say it is on Z1:

=MAX(LEN($A:$A))

Certify to press ctrl+shift+enter on the formula

Then you use this formula on your cells:=REPT(" ";Z1-LEN(A2))&A2

Edit: Sorry, anwsered late, teylyn is more complete.

Upvotes: 2

teylyn
teylyn

Reputation: 35915

Consider this screenshot:

enter image description here

In column B the length of each cell of column A is established with the formula =len(A1) copied down.

Cell D2 has the range name MaximumLength and the formula =max(B:B).

With that in place, you can create the padded values with this formula in cell G1, copied down:

=A1&REPT("*",MaximumLength-LEN(A1))

If you don't want to use the helper column and helper cell, you can use this array formula instead:

=A2&REPT("*",MAX(LEN(A1:A15))-LEN(A2))

This formula must be confirmed with Ctrl-Shift-Enter. It is advisable to use defined ranges, not whole columns in array formulas, hence the range in LEN(A1:A15). Adjust as desired.

I've used the "*" character so it is visible. Replace it with a space " " in your scenario.

Upvotes: 2

Related Questions