EXTRACT TEXT

[sna]

Hi Dear
I need your help how to write code to extract text from mixed number and text.
I have a code below.any one can help with simplified code

Code: Select all

Sub MyMacro()

Sheets("Sheet1"). Select 

'   Find last row in column D with data
    lr = Cells(Rows.Count, "D").End(xlUp).Row

'   Enter formulas
    Range("F2:F" & lr).FormulaR1C1 =  "=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RC[-2],1,""""),2,""""),3,""""),4,""""),5,""""),6,""""),7,""""),8,""""),9,""""),0,"""")"

End Sub

As well as provide me with excel 365 formula as well.

Thanks

[norkaz]

...

Hi Guy

I'm not a VBA guy, if you could extract text by using the formula -- the O365 spill array formula like this might be a good choice.

F2

=BYROW(D2:D4,LAMBDA(q,IF(q="","",CONCAT(TEXTSPLIT(q,ROW(1:10)-1)))))

Norkaz

[sna]

I come with this formula
=BYROW(D2:D4,LAMBDA(a,CONCAT(TEXTSPLIT(a,SEQUENCE(10,,0)))))

Thanks

[norkaz]

I come with this formula
=BYROW(D2:D4,LAMBDA(a,CONCAT(TEXTSPLIT(a,SEQUENCE(10,,0)))))

Thanks

Hi Guy,

Nice shot with SEQUENCE starting with 0 -- it would be better to take the IFERROR before CONCAT like this.

=BYROW(D2:D4,LAMBDA(a,IFERROR(CONCAT(TEXTSPLIT(a,SEQUENCE(10,,0))),"")))

and the MAP function can be replaced BYROW in this situation.

=MAP(D2:D4,LAMBDA(a,IFERROR(CONCAT(TEXTSPLIT(a,SEQUENCE(10,,0))),"")))

Norkaz

[sna]

tks Norkaz.it's really such a helpful formula