macro to output a string

[diageminc]

Hello,

Item	Qty		Output
P18456	2		P 18456 quantity 2
SP10458	1		SP 10458 quantity 1
SR10456-9	2		SR 10456 dash 9 quantity 2
E8986	4		E 8986 quantity 4

<tbody>
</tbody>

Please refer to the above table, we have the following formula to translate col A and Col B to the desired output
=REPLACE(SUBSTITUTE(A2,"-"," dash "),IF(ISNUMBER(VALUE((MID(A2,2,1)))),2,3),0," ")&" quantity "&B2

We are looking for a macro that can accomplish the same output instead of a formula.

Any ideas?

Thanks
Amit

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub ReplaceDashAffixQuantity()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("C2:C" & LastRow) = Evaluate(Replace("IF(A2:A#="""","""",SUBSTITUTE(REPLACE(A2:A#,3-ISNUMBER(0+MID(A2:A#,2,1)),0,"" ""),""-"","" dash "")&"" quantity ""&B2:B#)", "#", LastRow))
End Sub[/td]
[/tr]
[/table]

 

[diageminc]

Give this macro a try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub ReplaceDashAffixQuantity()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("C2:C" & LastRow) = Evaluate(Replace("IF(A2:A#="""","""",SUBSTITUTE(REPLACE(A2:A#,3-ISNUMBER(0+MID(A2:A#,2,1)),0,"" ""),""-"","" dash "")&"" quantity ""&B2:B#)", "#", LastRow))
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Thanks Rick,

this is perfect.

amit