Use of MID function for Variable Extraction
 

Hi,

I have a need to extract the figure before "%" symbol. I tried using the script below but unable to achieve it. I will appreciate any assistance on the right function.

Range("T" & z).Value = Mid(Range("M" & z), 1, 4)

14.20% USD FEDERAL GOVERNMENT BOND
5.20% USD FEDERAL GOVERNMENT BOND
14% USD FEDERAL GOVERNMENT BOND

Desired Result:
14.20
5.20
14

Use of MID function for Variable Extraction
 

Hi Kris,

Am Wed, 22 Jul 2015 04:18:20 -0700 (PDT) schrieb :

I have a need to extract the figure before "%" symbol. I tried using the script below but unable to achieve it. I will appreciate any assistance on the right function.

Range("T" & z).Value = Mid(Range("M" & z), 1, 4)

14.20% USD FEDERAL GOVERNMENT BOND
5.20% USD FEDERAL GOVERNMENT BOND
14% USD FEDERAL GOVERNMENT BOND

try:

LRow = Cells(Rows.Count, "M").End(xlUp).Row
For z = 1 To LRow
With Range("M" & z)
Range("T" & z) = Left(.Value, InStr(.Value, "%") - 1)
End With
Next

or

Dim LRow As Long, i As Long
Dim varCheck As Variant, varTmp As Variant

LRow = Cells(Rows.Count, "M").End(xlUp).Row
varCheck = Range("M1:M" & LRow)
For i = LBound(varCheck) To UBound(varCheck)
varTmp = Split(varCheck(i, 1), "%")
Range("T" & i) = varTmp(0)
Next


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Use of MID function for Variable Extraction
 

On Wednesday, July 22, 2015 at 12:18:29 PM UTC+1, wrote:
Hi,

I have a need to extract the figure before "%" symbol. I tried using the script below but unable to achieve it. I will appreciate any assistance on the right function.

Range("T" & z).Value = Mid(Range("M" & z), 1, 4)

14.20% USD FEDERAL GOVERNMENT BOND
5.20% USD FEDERAL GOVERNMENT BOND
14% USD FEDERAL GOVERNMENT BOND

Desired Result:
14.20
5.20
14

Many thanks