Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #107871

Re: Use of MID function for Variable Extraction

Show all headers | View raw

Hi Kris,

Am Wed, 22 Jul 2015 04:18:20 -0700 (PDT) schrieb kriskapella@gmail.com:

> 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

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Find similar | Unroll thread

Thread

Use of MID function for Variable Extraction kriskapella@gmail.com - 2015-07-22 04:18 -0700
  Re: Use of MID function for Variable Extraction Claus Busch <claus_busch@t-online.de> - 2015-07-22 13:30 +0200

csiph-web