LinkBack URL
About LinkBacks
Hi
I want to replace a laborious find, copy paste task, is a udf for text extraction possible where the text to be extracted is specified each time it's run?
thanks
Hi
I want to replace a laborious find, copy paste task, is a udf for text extraction possible where the text to be extracted is specified each time it's run?
thanks
A. UDF - is a custom function...it does not get "Run" like a normal macro
B. You are going to have to be a lot more specific
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
I'm trying to form links between 2 datasets and there's a free text field in each that has a common reference, although there are a lot of the common references, So I want a function I can apply to a helper column where I get to change the alphanumeric to be extracted
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).
There's a 2 sheet sample here, with columns in of the extracted data, there are maybe 30 different alphanumerics to be extracted into helper columns from maybe 120000 rows
here is what i ended up with
Please Login or Register to view this content.
Ext(LookAt As Range, lookFor As Range, Count As Integer)
3 conditions of function
lookat = what you want to look
lookFor = list of terms you want to look for....in this example i have a list of terms in the worksheet...suggest using a named range for this so its easily maintained
count = this is the equivalent of your UDF1, UDF2,UDF3, ie 1st match, 2nd match, etc
Hi humdingaling
This looks fantastic, myvba is very limited and even when reverse engineering something i struggle, using a named range will be better, as the example doesn't cover all search terms, plus i can do variations such as 2 Mbps and 2Mbps etc, which bit of the code do i replace with a named range?
thanks again, this is just the ticket
i've uploaded the file with what i mean by named range
added new sheet (not necessary for new sheet but seems cleaner to do so)
created named range called list
made it refer to A2:A16
update this named range and it will update all the formulas automatically where it is used
re:
nuances with having a space and not having a space (with Mbps for example)
i suppose mode coding can be done to draw out the answer
ie check if " " before Mbps if not then do this, etc
the section of the code that does that is the select case
essentially only look for Mbps (or Gbps, Sip Trunks...add more here if you have more terms like these)Please Login or Register to view this content.
ill have another look at it tomorrow
Thank you so much, this is really helpful and gives me some great wins, I'll look at the amendments and try and get my head around the code, I'll try and update the range and upload that too
Hi humdingaling
Thanks again for all of this i really appreciate the effort and the input, I'm going through the code, and I get the named range and how to manage/update that, what I can't see is where in the code it refers itself to "list", and also, bear with me here-VBA is a weakness of mine, if we're now referring to a range list why is there still the case part of the script with the three main instances (Mbps, Gbps, SIP Trunks) when these and the variants could just be in the list? Sorry if i'm asking the obvious here........
The CASE is used to determine the numeric values associated with the specific list values of "Mbps, Gbps , SIP Trunks".
the "lookFor" range in the code refers to the named range List
in Macro ....
Function Ext(LookAt As Range, lookFor As Range, Count As Integer)
In your cells ...
=ext($A2,list,G$1)
$A2 = LookAt
list = lookFor
G$1 = Count
Hope this helps.
why not just add 30 colums, one for each search term Stored in Row1, then use the cell formulaFormula:
Please Login or Register to view this content.
Last edited by Gregor y; 04-19-2016 at 04:49 PM. Reason: add $
If it'd make you feel better using my answer because of my street cred, then you can go ahead and clickAdd Reputation below to improve it.
i've gone thru and added comments on most of the code
also made a change on how terms that require Numeric prefix's are handled
hopefully this helps
Please Login or Register to view this content.
OK, ignore my previous about the count part, I've been doing more testing and think I've got that now, this works fine and gives me a run of extraction columns to work from, i think from here I can just adjust the list and run through additional columns until that count doesn't bring back results and then from there i can build my fourth condition
thanks again so much for your input, time and effort, it's more appreciated than you know
Last edited by jyadayada; 04-25-2016 at 02:08 PM. Reason: user error!
the count part works just like Small or Large formula does
not a problem
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
My understanding of small and large is as it sounds, the smallest or largest value, I've run counts 1-5 across 5 columns and had differing results across the columns, my assumption being that count 1 takes the first instance of a match from the list, 2 the 2nd and so on?
this already posted but in my refresh it looked like it hadn't...
Last edited by jyadayada; 04-26-2016 at 10:34 AM. Reason: duplication
yes
http://www.techonthenet.com/excel/formulas/small.php
Small gives you the smallest number in nth position of selected range
ie
small(range,1) will give you the smallest
small(range,2) will give you the second smallest
etc
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks