Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
May 25, 2023

Text After Delimiter

Dear Experts,                      Could you please help in below Query:- In Attached Excel, I want my data like in Column "B" only the Text after 4 spaces(Item xx), then in Column "C" the text aft...
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
May 25, 2023

anupambit1797 

Problem is that if a cell begins with 16 spaces, it also begins with 12 spaces and with 8 spaces and with 4 spaces.

 

In B2:

 

=IF((LEFT($A132,4*COLUMN()-4)=REPT(" ",4*COLUMN()-4))*(MID($A132,4*COLUMN()-3,1)<>" "),MID($A132,4*COLUMN()-3,1000),"")

 

Fill to the right to for example H2, then fill down.

  • anupambit1797's avatar
    anupambit1797
    Iron Contributor
    May 26, 2023
    Thanks Hans, is there any other smaller formula we may have?
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      May 26, 2023

      anupambit1797 

      Using Patrick2788 's idea:

       

      =IF((LEFT($A132,4*COLUMN()-4)=REPT(" ",4*COLUMN()-4))*(MID($A132,4*COLUMN()-3,1)<>" "),TRIM($A132),"")

Resources