How Excel Data Splits ?

[Porag]

Dear Excel Expert,

I've attached an excel sheet where I've Used Formula =LEFT(A2,FIND("/",A2)-1) for Removing "/" and Taken Left All Characters before "/"
Like Example Below

SIZE_IN SIZE_OUT
XXXL/XP XXXL
XXL/PP XXL
XS/P XS
XXXL XXXL
XXL XXL
XS XS
But when Cell value will come Like "XXXL" without "/" then Full Characters "XXXL" will come, only split Left all characters data when "/" come, yours help and advise highly expecting.

Thanks,
Porag

 

[Gargamelbarbosa]

Try like this in cell C2 and copy down.

=IFERROR(LEFT(A2,FIND("/",A2)-1);A2

 

[Porag]

Try like this in cell C2 and copy down.

=IFERROR(LEFT(A2,FIND("/",A2)-1);A2

Hi Gargamelbarbosa,

Thanks a lot for great help ,Now working fine.

Thanks
Porag

 

[GraH - Guido]

Slightly different,
=LEFT(A2,IFERROR(SEARCH("/",A2)-1,250))

Or, for those on 365
=TEXTBEFORE(A2,"/",,,1)

 

[bosco_yip]

Or,

In B2, formula copied down :

=LEFT(A2,FIND("/",A2&"/")-1)

 

[petergroft]

Select the cell or column that contains the text you want to split.

Select Data > Text to Columns.

In the Convert Text to Columns Wizard, select Delimited > Next.

Select the Delimiters for your data. For example, commas and Space. You can see a preview of your data in the Data preview window.

Select Next.

Select the Destination in your worksheet which is where you want the split data to appear.

Select Finish.

Greetings,
Peter

 

[Peter Bartholomew]

Some good solutions (I didn't notice this question the first time around).
Of course, as someone that lives and breathes dynamic arrays, I am bound to prefer the @Guido's solution.
Only I would take it further and avoid relative references that give an array of single cell formulae of indeterminate size when one array formula will do the same job - and automatically resizes to match the source data.

= TEXTBEFORE(sizeIn, "/",,,1)

I realise that, right now, it is me that is out on a limb. Perhaps the day will come when I find my approach is mainstream (how many years to go until Office 2019 is history?)

 

[AlanSidman]

just for grins, here is one more method with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Extracted Text Before Delimiter" = Table.TransformColumns(Source, {{"SIZE_IN", each Text.BeforeDelimiter(_, "/"), type text}})
in
    #"Extracted Text Before Delimiter"