Solved

find 12 characters in a string

Posted on 2014-10-22
22
94 Views
Last Modified: 2014-11-06
is it possible to find 12 characters within a string, [but with a catch]

I will have a lookup table that will have a list of shrortened currency codes, so US, GB, CH, HK etc.

I need to look up this table and the find the code within the string that starts with US or GB or CH etc

so if the cell contains this:

GSB142839981001 //TAG1410210509596 1710598400 US455760CW45 SALE [in column 'N in sheet called RawData]

I need to extract US455760CW45

The Static codes will be in a sheet called Static in column 'A'

The cell containing the data will be in a sheet called RawData in Column 'N'

and I need to put the resultant answer [US455760CW45] in column 'O'

Is this doable?

Thanks in advance
0
Comment
Question by:Jagwarman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 4
  • 2
  • +3
22 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40398652
Do u want VBA solution or formula?
0
 
LVL 7

Expert Comment

by:slubek
ID: 40398660
See attached file.
t5.xlsx
0
 

Author Comment

by:Jagwarman
ID: 40398692
ProfessorJimJam ideally VBA

thanks
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Jagwarman
ID: 40398693
Also the data I am looking for could be anywhere in the string and not in the position I put it
0
 

Author Comment

by:Jagwarman
ID: 40398697
slubek I have to use a Static data file because there will be many different ones and although I can see what you have done there I cannot put them across the top of the file we will be downloading from our system
0
 
LVL 7

Expert Comment

by:slubek
ID: 40398884
You can use addresses from different file in your formulas. So your formula can be in output file, and you don't even have to open input file to calculate it - it can stay as you got it (if I understand correctly what you mean by "Static data file").
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40399014
Jagwarman - is it feasible/possible that a currency code combination could occur before the required string that you wish to extract. For example, using the sample in the question, what if the GSB at the start of your sample was actually GBS? The formula/VBA would then find the GB first.

Thanks
Rob H
0
 

Author Comment

by:Jagwarman
ID: 40399020
Rob that is possible but that is a chance I have to take. I mentioned this exact same thing to the user but they are happy to go with the 80/20 scanario because if we can automate this process it will save a huge amout of time. My belief is if the GSB was input as GBS when they review the 12 digit Reference it will pop up as invalid anyway.

Thanks
0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 40399095
UDF

Function EXTRACT12(V) As String
    
    Dim i   As Long, Curr As String, x
    
    Curr = "US,GB,CH,HK"   '<< add more currency codes here
    
    If TypeOf V Is Range Then V = V.Value2
    x = Split(V)
    
    For i = 0 To UBound(x)
        If Len(x(i)) Then
            If InStr(1, Curr, Left(x(i), 2), 0) Then
                If Left(x(i), 3) Like "??#" Then
                    EXTRACT12 = x(i)
                    Exit Function
                End If
            End If
        End If
    Next
    
End Function

Open in new window


use like

=EXTRACT12(A2)

Kris
0
 

Author Comment

by:Jagwarman
ID: 40405797
krishnakrkc

thanks but I need to call the info from a Static Data table that the user would be able to update as and when necessary. I do not want them going inot the Macro code.
0
 

Author Comment

by:Jagwarman
ID: 40408189
Rob Hanson or krishnakrkc will you be picking up on this one for me?
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 40417898
try this code as  function..

it looks for space + code  + space
Option Explicit
Function GetCurrencyCode(testCell As Range)

    Dim myValue As String, curTxt As String
    GetCurrencyCode = "Not found"
    Dim xCell As Range, ix As Integer
    Dim vbSpace As String
    vbSpace = Chr$(32)
    myValue = testCell.Value
    
    'try to find a currency code in the text (preceded by space)
    For Each xCell In Range("CurrencyTable")
        curTxt = xCell.Value
        ix = InStr(1, myValue, vbSpace & curTxt)
        If ix > 0 Then
            'found the code at least
            
            'now check for exactly 10 characters before next space
            If InStr(ix + 2, myValue, vbSpace) = ix + 13 Then
                'extract the data
                GetCurrencyCode = Mid$(myValue, ix + 1, 12)
                Exit Function
            End If
        End If
        
    Next xCell
    
End Function

Open in new window

findcurr-v2.xlsm
0
 

Author Comment

by:Jagwarman
ID: 40419534
Robberbaron

It is proberblt me but when I add new codes into the Tab Static it does not pick upi items starting with these codes. If I put the new code in cells A2, 3 or 4 it works fine but if I put the code in any other cell it does not work
0
 

Author Comment

by:Jagwarman
ID: 40419549
sorry one more thing. It seems like it does not like some codes.

 DK1234567811       DK1234567811
 CK1234567811       Not found
 JK1234567811       Not found


Static

CurrencyTable
US
DK
CH
HK
XS
JK
DK
CK

So DK works because it is in A3
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 40421063
you have to extend the named range.
inset new rows in the middle of the range (ie between US & DK), as this automatically extends it.  then copy the new currency codes into the blank cells.

it should then update the found codes list.
findcurr-v3.xlsm
0
 

Author Comment

by:Jagwarman
ID: 40421593
brilliant thanks
0
 

Author Comment

by:Jagwarman
ID: 40423382
Rob I tried to 'accept' but for some reason it didn't work. In the meantime I have another problem. For some reason even if the two digit code is not in the Static table it is selecting the data from column N if there is a space after 12 characters.

i.e.

142909767.00 1 //8678008 and it selects 142909767.00
NCBHKH426598 8201 and it selects NCBHKH426598

any ideas ?

Thanks for your help
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 40423644
i cant reproduce your problem. see attached sample with the data you indicated.

perhaps you need to load up the sample workbook with more real data and post it so can check further.
findcurr-v3a.xlsm
0
 

Author Comment

by:Jagwarman
ID: 40423739
Very odd. Thanks file attached
findcurr-v3b.xlsm
0
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 500 total points
ID: 40423876
ok, the reason was that you added blank cells to the CurrencyTable.

you want this table to be as small as possible to keep speed ok as it search's for each and every cell, incl blank ones.

I have added a check for blank that fixes the issue for now
    'try to find a currency code in the text (preceded by space)
    For Each xCell In Range("CurrencyTable")
        curTxt = xCell.Value
        ix = InStr(1, myValue, vbSpace & curTxt)
        If ix > 0 And curTxt <> "" Then    '<<<<added check for curTxt is empty
            'found the code at least
            
            'now check for exactly 10 characters before next space
            If InStr(ix + 2, myValue, vbSpace) = ix + 13 Then
                'extract the data
                GetCurrencyCode = Mid$(myValue, ix + 1, 12)
                Exit Function
            End If
        End If
        
    Next xCell

Open in new window

0
 

Author Comment

by:Jagwarman
ID: 40424114
Thanks Rob I think you have cracked. Thank you for your patience. Great Job
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question