How do you split contents in a cell with variable characters

[smerrick]

Hello,

I have a list of the following:

Account.Product Description.Product Code

(notice the dots separating each field)

is there a formula that I can use that would link to these cells and get the desired info e.g. product description or code only? Note that the number of characters do vary and currently using excel 2003.

Many Thanks

 

[kidwispa]

Hello,

I have a list of the following:

Account.Product Description.Product Code

(notice the dots separating each field)

is there a formula that I can use that would link to these cells and get the desired info e.g. product description or code only? Note that the number of characters do vary and currently using excel 2003.

Many Thanks

Would using Text to Columns to split the data using the "." as a delimiter be an option?

 

[smerrick]

no, looking for something like =MID(D2,1,FIND(".",D2,1)-1) but need to be able middle and right hand sections also (the above would calculate the left bit only

 

[Ruddles]

The left bit:-

=TRIM(LEFT(SUBSTITUTE(A1,".",REPT(" ",99)),99))

The middle bit:-

=TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",99)),99,99))

The right bit:-

=TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",99)),99))

(Tested quickly here. Satisfy yourself they work with a range of inputs before relying on them.)

 

[smerrick]

Ruddles,

exactly what I was after - thank you!

 

[BeingYogi]

May Be

=MID(A1,1,FIND(".",A1)-1)
=MID(A1,FIND(".",A1)+1,(FIND(".",A1,FIND(".",A1)+1)-FIND(".",A1)-1))
=MID(A1,FIND(".",A1,FIND(".",A1)+1)+1,99)