Concatenate cells with 5 characters each column but ignore if blank

[ghiegomez]

Hi all,

good morning! I'm in need of help to figure out how to concatenate columns by a dash with 5 character each column but if the column is empty the formula must ignore move on to the next column.


Columns a | b | c | d | e | f | g | h |

this is how it should be concatenated and please note the length value inside the column should 5 so i need automatic leading zero if my value each col is less than 5:

Column grouping: A-b | c-d | e-f | g-h

a | b | c | d | e | f | g | h |
blk1 | blk2 | blk3 | | blk5 | blk6 | blk7 | |

output should be: 0blk1-0blk2 0blk3 0blk5-0blk6 0blk7

please help!

[FDibbins]

Hi and welcome to the forum

Sometimes, when you copy/paste your data into here, it doesnt come out quite the way you intended I suggest you upload this in a dummy workbook

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[ghiegomez]

Concatenate.xlsx

I'm sorry. Here you go... Thank you!

[martindwilson]

First attempt
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A3&" "&B3&"^"&C3&" "&D3&"^"&E3&" "&F3&"^"&G3&" "&H3)," ","-"),"-^"," "),"^"," "),"BLK","0BLK")

[ghiegomez]

I apologize for not being clear. Actually "BLK" is just one of the words on spreadsheet. It could be any word or data.

[ChemistB]

It's not pretty but

=SUBSTITUTE(TRIM(SUBSTITUTE(TRIM(REPT("0",5-LEN(A3))&A3&" " & REPT("0",5-LEN(B3))&B3&" " &REPT("0",5-LEN(C3))&C3&" " &REPT("0",5-LEN(D3))&D3&" " &REPT("0",5-LEN(E3))&E3&" " &REPT("0",5-LEN(F3))&F3&" " &REPT("0",5-LEN(G3))&G3 &REPT("0",5-LEN(H3))&H3),"00000", ""))," ","-")

[martindwilson]

yea i stopped for dinner and came up with
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(REPT("0",IF(5-LEN(A3)=5,0,5-LEN(A3)))&A3&" "&REPT("0",IF(5-LEN(B3)=5,0,5-LEN(B3)))&B3&"^"&REPT("0",IF(5-LEN(C3)=5,0,5-LEN(C3)))&C3&" "&REPT("0",IF(5-LEN(D3)=5,0,5-LEN(D3)))&D3&"^"&REPT("0",IF(5-LEN(E3)=5,0,5-LEN(E3)))&E3&" "&REPT("0",IF(5-LEN(F3)=5,0,5-LEN(F3)))&F3&"^"&REPT("0",IF(5-LEN(G3)=5,0,5-LEN(G3)))&G3&" "&REPT("0",IF(5-LEN(H3)=5,0,5-LEN(H3)))&H3)," ","-"),"-^"," "),"^"," ")

[ghiegomez]

Still not working when I removed a value on one column it combines it with another column.
A-B should be concatenated together and B-C should always have a space in between and not a dash if one cell if empty. Notice how 0BLK6-0BLK7 got combined together? it should have been 0BLK6 0BLK7

Result of the formula: 0BLK1-0BLK2-0BLK3-0BLK5-0BLK6-0BLK7

[martindwilson]

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(REPT("0",IF(5-LEN(A3)=5,0,5-LEN(A3)))&A3&" "&REPT("0",IF(5-LEN(B3)=5,0,5-LEN(B3)))&B3&"^"&REPT("0",IF(5-LEN(C3)=5,0,5-LEN(C3)))&C3&" "&REPT("0",IF(5-LEN(D3)=5,0,5-LEN(D3)))&D3&"^"&REPT("0",IF(5-LEN(E3)=5,0,5-LEN(E3)))&E3&" "&REPT("0",IF(5-LEN(F3)=5,0,5-LEN(F3)))&F3&"^"&REPT("0",IF(5-LEN(G3)=5,0,5-LEN(G3)))&G3&" "&REPT("0",IF(5-LEN(H3)=5,0,5-LEN(H3)))&H3)," ","-"),"-^"," "),"^"," ")," -"," ") thats about it the formula gets too long otherwise
or put that in i3 then in j3 =trim(i3)

[ghiegomez]

Sorry but the formula doesn't work

[martindwilson]

post a workbook where it doesnt work

[ghiegomez]

Sorry, it did work this time. I must have copied an extra space. Just wanted to understand your formula what's the ^ for? and also, is there a way the extra spaces at the end of the formula be removed instead of the additional =trim() formula?

Thank you very much for all your help! really really really appreciate it!

[martindwilson]

not in 2003 the formula is at its max length if you try to add trim to it it doesn't work

[ghiegomez]

How about for 2010? Do you mind helping me with 2010 version?

[martindwilson]

I haven't got 2010 but try wRaping it in trim

[tigeravatar]

Alternate:

=SUBSTITUTE(RIGHT("00000"&A3,5)&"-"&RIGHT("00000"&B3,5)&" "&RIGHT("00000"&C3,5)&"-"&RIGHT("00000"&D3,5)&" "&RIGHT("00000"&E3,5)&"-"&RIGHT("00000"&F3,5)&" "&RIGHT("00000"&G3,5)&"-"&RIGHT("00000"&H3,5),"-00000","")

[ghiegomez]

@martindwilson - you are awesome! you just made my life easier! You were a great help! thank you so much!

[ghiegomez]

Hello @Martindwilson...

I'm going to revive the formula you helped me with back in 2013 -- I just wanted to understand the use of "^"? Can you please help?

=TRIM(CONCATENATE("CAVENT:",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(REPT("0",IF(4-LEN(F2)=4,0,4-LEN(F2)))&F2&"^"&W2&"-"&REPT("0",IF(5-LEN(X2)=5,0,5-LEN(X2)))&X2&"^"&REPT("0",IF(3-LEN(BP2)=3,0,3-LEN(BP2)))&BP2&"^"&REPT("0",IF(5-LEN(BR2)=5,0,5-LEN(BR2)))&BR2&" "&REPT("0",IF(5-LEN(BS2)=5,0,5-LEN(BS2)))&BS2&"^"&REPT("0",IF(5-LEN(BT2)=5,0,5-LEN(BT2)))&BT2&" "&REPT("0",IF(5-LEN(BU2)=5,0,5-LEN(BU2)))&BU2&"^"&REPT("0",IF(5-LEN(BV2)=5,0,5-LEN(BV2)))&BV2&" "&REPT("0",IF(5-LEN(BW2)=5,0,5-LEN(BW2)))&BW2&"^"&REPT("0",IF(4-LEN(BX2)=4,0,4-LEN(BX2)))&BX2&" "&REPT("0",IF(4-LEN(BY2)=4,0,4-LEN(BY2)))&BY2)," ","-"),"-^"," "),"^"," ")," -"," ")))

[FDibbins]

Hello @Martindwilson...

I'm going to revive the formula you helped me with back in 201...

No, you are not. As this is over 7 years old, please start a new thread, and reference this thread if necessary