Converting Text Strings

[macka1987]

Morning all,

Yesterday I posed a question which is proving to be difficult to code, however last night I had a brain wave on how I could do it, however now I need some help coding the idea!

I have a spreadsheet C5:DD22, which contains data in the following format:

Linda+1; George+2; Albert+4
Albert+3
George+4; Albert+6
Linda+1; Albert+4
Linda+3; George+2; Albert+5

The names Linda, George, Albert appear in that order always. The only exception to this is if the name does not appear at all, i.e. Albert+3 and nothing else.

I would like a function that converts these five examples, into the following text strings:

Linda+1; George+2; Albert+4
Linda+0; George+0; Albert+3
Linda+0; George+4; Albert+6
Linda+1; George+0; Albert+4
Linda+3; George+2; Albert+5

Many thanks in advance!

[teylyn]

Can you please post a workbook with the data? There's no way of telling if these values are in a single cell or spread across the row.

Also, mock up the expected results in the sample file, so we know what to compare our suggestions with.

[macka1987]

Many thanks teylyn for resonding so quickly.

I have attached a spreadsheet which contains the raw data and an example of the output required.

Cheers,

[teylyn]

Try in E5 and copy down

Please Login or Register  to view this content.

[martindwilson]

why do you insist on them being in one cell its illogical!