Friday, February 22, 2013

Excel Function extraction based on delimeter

Good day,

Today i am presented with thousand entries of data and i am interested in presenting information based on the content of two columns in this nature.

This is like pulling first name and surname stuff only i am doing a conditional concatenation of the source data from two different sources.

Column 1   Column 2
A/B             1,C
A/B             2
A/B             3
A/B             4,D
A/B             5,E
A/B            6
A/B            7,F
A/B            8,G
A/B            9,H


And the logic is if Column 2 has a comma -> content after C should be substituted for content in Column 1 after the forward slash "/"

let column1 be C5,C6....
and column 2 be D5,D6.....


=CONCATENATE(TRIM(LEFT(C5,FIND("/",C5))),IFERROR(TRIM(RIGHT(D5,LEN(D5)-FIND(",",D5))),TRIM(RIGHT(C5,LEN(C5)-FIND("/",C5)))))

trim is put in to eliminate any white space that may occur.

This formula does it, constraint column 1 must always have a "/" present, however a logic case can be built like as applies to column 2 to take care of the absence or presence of a "/" in column 1.

Cheers!
Kodako!

No comments:

Post a Comment

Check Care Confirm Correct

 As I glance through the pages of a book I'm reading, I learn one crucial aspect of human interaction that is worth observing and modula...