Excel Forum

Posted: **Sat Jun 10, 2023 6:21 am**

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

Posted: **Sat Jun 10, 2023 11:38 pm**

...

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

Posted: **Wed Jun 14, 2023 2:40 pm**

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

Thanks

Posted: **Wed Jun 14, 2023 3:12 pm**

sna wrote: Wed Jun 14, 2023 2:40 pm 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

Posted: **Sun Jun 18, 2023 2:08 pm**

tks Norkaz.it's really such a helpful formula

Excel Forum

EXTRACT TEXT

EXTRACT TEXT

Re: EXTRACT TEXT

Re: EXTRACT TEXT

Re: EXTRACT TEXT

Re: EXTRACT TEXT