Forum Discussion

Sridhar0311's avatar
Sridhar0311
Copper Contributor
Nov 03, 2020
Solved

How to Separate data using identifier

Hi All,

 

I want to extract data from a specific cell by using an Identifier like ( , . - ).

 

For Example : From  "School.Data.Boys.Girls.Parents" , I need to extract "School.Data.Boys.Girls" In one cell & "Parents" in another cell.

 

Any Insights are highly appreciated. 

 

Thanks

Sridhar 

excel
Excel on Mac
Excel on mobile
Formulas and Functions
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Nov 03, 2020

    Sridhar0311 

    Let's say the text is in A1.

    To get the first part:

    =TRIM(LEFT(SUBSTITUTE(A1,".",REPT(" ",255),LEN(A1)-LEN(SUBSTITUTE(A1,".",""))),255))

    and for the last part:

    =TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",255),LEN(A1)-LEN(SUBSTITUTE(A1,".",""))),255))

    These formulas can be filled down if required.

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 03, 2020

    Sridhar0311 

    Let's say the text is in A1.

    To get the first part:

    =TRIM(LEFT(SUBSTITUTE(A1,".",REPT(" ",255),LEN(A1)-LEN(SUBSTITUTE(A1,".",""))),255))

    and for the last part:

    =TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",255),LEN(A1)-LEN(SUBSTITUTE(A1,".",""))),255))

    These formulas can be filled down if required.

Resources