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