CODEWITHSUNDEEP
google-sheetsarray-formulasgoogle-sheets-formulags-vlookup
Ralph Schipper
Ralph Schipper

Reputation: 741

VLOOKUP remove spaces when cell is empty

This a simple customer sheet:

A   B       C       D
ID  First   Middle  Last
1   John            Doe
2   Jane    Maia    Doe

And in F1 I put this vlookup code:

=VLOOKUP($G$1;$A$1:$D$3;2;FALSE)&" "&VLOOKUP($G$1;$A$1:$D$3;3;FALSE)&" "&VLOOKUP($G$1;$A$1:$D$3;4;FALSE)

When I lookup ID 2, it's perfect nicely spaced between the vlookups

But when I lookup ID 1 you see 2 spaces between the first and last name, because there is no middle name here.

How can I manage that I always see 1 space between the vlookups?

Upvotes: 0

Views: 238

Answers (2)

player0
player0

Reputation: 1

all you need is TRIM fx and:

=ARRAYFORMULA(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IFERROR(
 VLOOKUP(G1:G2, A1:D3, {2,3,4}, 0))),,999^99))))

0

Upvotes: 1

Dave Meindl
Dave Meindl

Reputation: 121

One way you could achieve the result you're looking for is to simply replace multiple spaces with a single space.

=REGEXREPLACE(JOIN(" ",ARRAYFORMULA(VLOOKUP(G1,A:D,{2,3,4},FALSE))),"\s{2,}"," ")

This formula looks up G1 in your table (A:D). VLOOKUP can be used in an ARRAYFORMULA to efficiently retrieve all of the columns you want in one shot. Your JOIN joins all of the retrieved columns, inserting a space between each value. Finally, your REGEXREPLACE function looks for multiple consecutive spaces and replaces them with a single space.

Alternatively, you could filter the resulting array (i.e. the result of what your VLOOKUP returns). The following formula looks up the array of first, middle, and last name, and then filters out any empty cells before joining the remaining elements with a space.

=JOIN(" ",FILTER(VLOOKUP(I1,A:D,{2,3,4},FALSE),INDIRECT("B"&MATCH(I1,A:A,0)&":D"&MATCH(I1,A:A,0))<>""))

Upvotes: 2

Related Questions