CODEWITHSUNDEEP
excelmerge
Unicorn
Unicorn

Reputation: 109

excel, merge two columns between static column and list of numbers

column A, static value:

302_000
302_000
302_000

column B, list of numbers, separated by . (except is there is only 1 number in the cell):

222. 57. 5.
389. 390. 
412

column c, after merge:

302_000_222, 302_000_057, 302_000_005
302_000_389, 302_000_390
302_000_412

any recommendations?

Upvotes: 0

Views: 100

Answers (2)

Ilia Gilmijarow
Ilia Gilmijarow

Reputation: 1020

=A1 & SUBSTITUTE(B2, ". ", ", "&A1)

This prefixes the value with the constant, and then replaces each dot with a comma and that constant

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152585

In C2 put the array formula:

=TEXTJOIN(",",,A2&"_"&TEXT(TRIM(MID(SUBSTITUTE(LEFT(TRIM(B2),LEN(TRIM(B2))-1),".",REPT(" ",99)),(ROW($ZZ$1:INDEX($ZZ:$ZZ,LEN(LEFT(TRIM(B2),LEN(TRIM(B2))-1))-LEN(SUBSTITUTE(LEFT(TRIM(B2),LEN(TRIM(B2))-1),".",""))+1))-1)*99+1,99)),"000"))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter.

enter image description here

Upvotes: 0

Related Questions