Text manipulation
Hi
My knowledge is just scratching the surface of what is possible with Excel so I need help please.
In each Row of a spreadsheet I have two adjacent cells in the following formats
NUMBER1{},NUMBER2{},NUMBER3{},NUMBER4{}……..NUMBERn{} where 'n' could be up to 35.(Mostly less majority less than 10)
e.g.: 2156{},976{},33{},21591{}
and
TXT1///TXT2///TXT3///TXT4…..///TXTn where 'n' is the same value in both cells.
e.g.: RED///YELLOW///GREEN///BLUE
The result I am trying to achieve should be stored in the same Row in the form:-
NUMBER1{TXT1},NUMBER2{TXT2},NUMBER3{TXT3},NUMBER4{TXT4}……..NUMBERn{TXTn)
e,g from above inputs:
2156{RED},976{YELLOW},33{GREEN},21591{BLUE}
Can anyone advise on how this might be done please ? Better still can anyone construct the solution?
Many thanks
Rob
put this in col
put this in col A:
NUMBER1{},NUMBER2{},NUMBER3{},NUMBER4{}……..NUMBERn{}
Put this in col AK:
TXT1///TXT2///TXT3///TXT4…..///TXTn
select col A and go:
Data=>texttocolumns... choose "," as your separator.. press Finish
- this will separate out the text
do the same for col AK..
then write a formula to combine the data into the required format..
something like:
=left(A1,len(a1)-1))&Ak1&"}"
which removes the "}" then adds the txt, then closes the "}"