How To Extract Substring From String Based On Substring Length
Jan 16, 2008
(I need to do this using VBA so that it can work as an excel macro and/or a vba function in access.)
How can I extract the first occurance of any substring over a certain character length from within a string ?
eg. My string may take any of the forms given below (or something similar), how can I extract the first set of characters that is at least (say) 4 characters long ? i.e. always get the 'Phillips' out of the below...
Phillips Homes Ltd
Mr T A Phillips
TA Phillips Homes Ltd
T A Phillips Homes Ltd
T. A. Phillips Homes Ltd
The names used are only an example (Phillips could be any name whatsoever).I am familiar with Search, Len, Left, Right, Mid, Instr etc. but just can't figure this one out.
View 9 Replies
ADVERTISEMENT
Jul 19, 2012
I've found several posts about returning variable substrings that dealt with one or two spaces, but I have not been able to find anything that is for multiple spaces. The number of characters before the substring will remain constant.
For example:
Income from transmission agreement - 83 subs @ $0.44
In this case I need to extract: 83
Income from transmission agreement - 10,312 subs @ $0.50
Need: 10,312
View 8 Replies
View Related
Nov 26, 2008
I am trying to make a simple formula to populate a cell with the first matching substring found in a data cell (from a fixed list of substring keys).
Example of desired results:
Substring data: {X, YY, ZZ} (typically 6 to 15 values in real world use)
Data..........Calculated result..........Comment
AAX.................X........................Substring X was found in Data.
ABC.............[empty]...................None of substrings were (X, YY, ZZ were found)
XYYZZ.............X......................(only first match need be returned)
ABCZZ.............ZZ....................Substring ZZ found etc….
I am looking for something cleaner than nested if statements since there are likely to be a lot of values and different variations. The work would then be to copy the formula and populate the list of substrings to search for in the data. I tried some array formulas using search() but no luck so far.
View 4 Replies
View Related
Apr 2, 2014
I am trying to extract the last substring in some text that I have. see below for example.
12/30-12/31 Bob Dylan 40.25
I need to extract the 40.25 out. Every line item may be different but the number I am extracting is always after the last space. I tried using mid and find but this doesn't work well when there is more than 1 space in the text.
View 4 Replies
View Related
Jan 6, 2014
Let us say I have the following text in Cell A1. "There is a fault in the cal cycle.Need to update the records." And in cell A2 I have the following text. "Called for backup assistance. There is an issue with numbers." I cells B1 & B2 I need a particular formula which searches for the substring "cal" exactly and returns true if present. In cell A1, we have "cal" in the text. So it should return true for me in cell B1.
However in cell B2 I need false to be returned even though I have "Called" inside the text. I need true to be shown only for those cells where we have the exact text "cal" and no text characters in front or back of it.
View 3 Replies
View Related
Jan 16, 2010
I'm looking for a formula that can determine whether a given sub-string appears in a string. For example if cell A1 contains the string "bathing", and cell A2 contains the sub-string "bat", the formula in A3 would return "TRUE".
However, if the sub-string in A2 is changed to "cat", the result in A3 would be "FALSE". The formula would need to work with strings of different and unpredictable lengths.
View 2 Replies
View Related
Dec 20, 2013
I need to export this to Xcelsius which doesn't support any macros/vba. Btw I can;'t use Row() in xcelsius too.
[Code].....
View 4 Replies
View Related
Jan 13, 2009
I have attached the 2 workbook, master & transaction. From master workbook column F column name "EXIST DOCUMENT" values need to find in the transaction workbook column D column name "Documents". If match found in the column D substring field, it need to replace with column G column name "NEW DOCUMENT" value from master workbook.
The transaction workbook need to be given as input workbook, when the macro is get executed from master workbook.
View 2 Replies
View Related
May 20, 2002
I wish to search for a substring within an array of strings and just return back an indication that the substring exists withing that array.
so for instance let the substring be "rang"
and let the array be
{"apple","orange","lemon"}.
I wish to put a single command line next to my substring and wish to search through that range and return a key code if it exists. And then I will repeat the same search for other substrings. For the moment being, I don't care too much about the number of occurences or the exact cell address where they occur. All I wish to know is whether they exist in that list or not.
So it looks to me it is like a SEARCH command but except on a range of text (Rather than a single text) with an array formula. I have tried MATCH but my case is not an exact match either.
View 9 Replies
View Related
Sep 16, 2012
Using Excel 2010 Pro.
I have a formula to create a string to create a lable for a Trial Balance report
=LEFT(I5&" "&REPT(".",95),101)
But the decimal portion of the string is distracting. I would like to make it a grey instaed of a black so that the text portion of the label "pops".
I don't believe I can do it with conditional formatting and I do not want to do it manually.I was thinking some kind of function or UDF like:
=LEFT(I5&" "&RGB(REPT(".",95),(191,191,191)),101)
View 3 Replies
View Related
Dec 6, 2013
I have figured out certain formulas that can get me pieces of the text substring, I am having difficulty finding an all in one solution that will allow me to run a macro and automate this process due to the nature of variability of the # of characters in the text string. The category and what I need to extract is as follows (Michigan Missing Zips roll up I can deal with):
MWR - Illinois - HORIZONTAL LATHES
MWR - Illinois
MWR - Illinois - MACHINING CENTERS - HORIZONTAL & OTHER
MWR - Illinois
MWR - Illinois - MACHINING CENTERS - VERTICAL
MWR - Illinois
[Code] ..............
View 3 Replies
View Related
Feb 9, 2014
i just wanna extract the date shown(last data until meet the first comma) in first column to another column.
column 1
abcde, 1234
abcd, 12345
abcdef, 123
column 2
1234
12345
123
View 3 Replies
View Related
Jan 25, 2007
Imagine that I have this list of thousands of customers, who are listed in column B in the format "customer no+space+customer name".
Now I want to extract ONLY the customer no from the text string in column B and insert it as number value in column A on the same row.
Would have been easy with "Left" command, but as the customer numbers have variable length, I have a problem.
View 5 Replies
View Related
Sep 1, 2009
I get all my results from a CSV file. This isn’t a problem except for the first nine days of the month where excel cuts off the first 0 so 01012009 reads 1012009 and cannot be properly concatenated.
DateNameItem
1012009BobKeyboard
1012009SidMouse
1012009DaveMonitor
1012009EdMonitor
1012009AndrewKeyboard
1012009DanKeyboard
1012009BruceKeyboard
What I would like to do is have VB read the contents of the date cell and if it is less than eight characters, edit the cell by adding a zero at the beginning of the string. This needs to be a one off process for all cells. Can anyone come up with code that might make this happen?
View 9 Replies
View Related
Mar 27, 2014
I've set up a filing system which saves sheets/ workbooks based on the value of a cell - Range("B1") Everything works great apart from when ThisFile String length exceeds 31 characters which you may know is the max useable character length for a sheet name - I had no idea! 8-0
Is there a way i can check if string length exceeds 31 characters then, if it does, shorten it to 31 characters?
[Code] .....
View 2 Replies
View Related
Jan 9, 2010
My worksheet contains data with the reaction times on a psychological test. Each respondent in the test has 280 rows in my excel sheet.
The 'perfect' length of the row, is from A to M. When an error is made in the test, the length of the row will increase. So the error length can be A to AA.
For me it is important to analyse the error. So I would like to give a perfect row length, the value 1, and an error row length a value 2.
So, in conclusion:
If:
Cell length = A1 - M1? --> Copy A1 B1 C1 (A B Cof that row) to Sheet3, and give D1 in sheet 3 the value 1
Cell length >= A1 - M1? --> Copy A1 B1 C1 (A B C of that row) to Sheet3, and give D1 in sheet 3 the value 2
View 9 Replies
View Related
Dec 5, 2013
I would like to extract competition naps from the competition entries and then remove the string ending "nap" from the data so it can be pasted to another sheet in it's 'clean' format.
on the example sheet column B will always be the first column and the range will extend to either G,H or I (always the max range as there are always 6 to 8 races)
i would like the code to search through these columns of data and find the cell ending in 'nap' then to return this in column i, once done remove 'nap' ending from both the source cell and column i.
once done to then go through all the rows and do similar
the end result would be all naps returned into cell j and all the 'nap' endings through the data range B to I (max) to be removed to leave the horses name only.
in book 3 the first row nap was sommersturm so i have shown the outcome i would like with the nap ending in I1 removed and the horse name returned in J1 again minus 'nap' ending.
the data will always be clean with no leading/trailing/excessive spaces and always be lower case too. sometimes a space is not in between horse name and nap but it's always last 3 characters i want removed still.
View 4 Replies
View Related
Jan 18, 2008
I have the following issue. I have a list of strings. Each string contains certain characters that are exactly the same for all strings, some characters are different making the string longer (in some cases). What I need to do is extract some combination of characters from each string. The strings look like this:
AB & CDE & FG & I mmmm yyyy.HIJK
AB & CDE & FG & II mmmm yyyy.HIJK
The part "AB & CDE & FG & " (incl. spaces) is the same for each string. The next part contains a roman count from I to VII, causing the length of each string to vary. The "mmmm" part contains the current month spelled in full e.g. December. This part differs as well, for each string. The "yyyy" part contains the year in four digits, e.g. 2007. The part after the dot is the same for each string again.
What I need to do:
- is to extract the month and assign it to a new string
- extract the year and assign it to a new string
- extract the roman number, translate it to a normal number (II -> 2) and assign to a string (or integer).
View 2 Replies
View Related
Dec 1, 2009
I am trying to create a string of text that grabs info from other cells, which is easy so for example
=A1&A2&A3
would grab all the info from the 3 cells and merge them to create a string, but what I want to do is create a prefixed length.
Lets say A1, A2 and A3 would equal 10 characters in total but B1, B2 and B3 would equal 7 characters, these would be different lengths. so for a visual description I would like them to appear like this
At the moment
01MAIN-HELLO
01DESSERT-HELLO WORLD
Would like
01MAINXXX-HELLO
01DESSERT-HELLO WORLD
This would make both strings the same length and would make it easier for me to export to a custom file that needs a certain length prefix.
View 9 Replies
View Related
Apr 8, 2013
I have a row of 1's and blanks, where there might be 10 consecutive 1's, followed by four consecutive blanks. I want to calculate the average length of a string of 1's in the row -- how this might be accomplished?
View 7 Replies
View Related
May 24, 2014
a formula to extract the numbers into two separate fields. The text may vary in length and the numbers vary in length also from 1 digit to 2,000,000.
Data in A1 is as follows:-
Meter reading Old:1345 New:67890
View 9 Replies
View Related
May 21, 2012
I have an ODBC connection to a db2 database in MSQuery. I want to return the length of a string. I've tried Len(string) but it says its not in the library.
View 2 Replies
View Related
Sep 28, 2006
The following afterupdate procedure for my txtStart1 text box sends the value to the FormatTimeValue function that is coded in a module.
Private Sub txtStart1_AfterUpdate()
MsgBox Len(Trim(Me.txtStart1.vaue)) '<= outputs 1
Me.txtStart1.Value = FormatTimeValue(Trim(Me.txtStart1.Value))
End Sub
As you can see from the bolded text, the length of the value is 1 when outputted from the forms code.
However, when I pass it to the following function in a module it outputs 2 as the length.
Function FormatTimeValue(vTarget As Integer) As String
Dim TimeStr As String
If IsNumeric(vTarget) Then
MsgBox Len(vTarget) '<= outputs 2
Select Case Len(vTarget)
Case 1 ' e.g., user entered 1 so time should be 01:00
TimeStr = "0" & vTarget & ":00"
View 3 Replies
View Related
Aug 10, 2007
I have looked into the maximum length of a variant/string in vba and it appears to be 250 characters. I am running a macro which first lists all excel files in a folder, returning them to a sheet, then using a loop statement opens each one in turn extracting the information to a second summary sheet before closing it. The file path to the folder is ridiculously long and the macro stumbles. I used the =LEN(A1) formula to check if the file names were too long for the string, but the maximum file name length was 226 characters. I've tried both String and Variant to collect the file names but both have the same effect.
View 2 Replies
View Related
Jun 6, 2008
I need to insert 00 if the string of text = 13 in a specific column.
eg. Column K can contain any amount of lines of data. The data will either be 15 or 13 characters in length. If its length is 15 its fine, if its 13 I need to get it to 15 characters.
The first 6 characters are letters so if the length is only 13 I need to add the 00 in after the last letter.
View 3 Replies
View Related
Mar 28, 2006
I am trying to split up a cell into numbers and charachters and place them in
separate columns, but the lenght of the number part varies as does the
content of the character part.
For example, one cell could be 5#, 10Tins, 4 lb, 100Pcs, etc.
I would like to be able to pull out the #, Tins, lb, and Pcs in to their
own column.
I have found this formula:
=LEFT(A1,FIND("-",A1,1)-1)
but it assumes some level of consistency, the "-" in the cell.
View 18 Replies
View Related
Apr 8, 2013
I have a set of data in Which in column A is the name of organisation.
If string in col A is longer than 50 I need to split in and put in col B.
That would be simple however I need to do it in a smart manner: i.e. cut it to the nearest full word.
Example:
THIS EXAMPLE NAME IS TOO LONG TO FIT INTO 50-TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO STRINGS
Incorrect; length = 98
THIS EXAMPLE NAME IS TOO LONG TO FIT INTO 50-TEXT
Correct; trimmed down to 48.
My question is about formula that can detect spaces and depending on those trim the string down adequatly:
to 50 if 50th char is preceeded by space; if not then check where is the next space going towards left. Once you find it cut the string there.
What formula I can use to get desired effect?
View 9 Replies
View Related
Jan 13, 2009
Hey I got a long String like this "[...] increase of x.xx% [...]".
I am trying to extract only the percentage number which can be of variable length, so maybe 900.99% or 9.99%.
I tried this formula:
=MID(G14,SEARCH("%",G14)-5,5)
but this one doesnt bring the right results as the percentage figure is often not exactly 5 characters long.
View 9 Replies
View Related
Jun 23, 2006
I have a spreadsheet with 2 sheets. The first just links to another file and pulls in the contents of a particular sheet, cell for cell. The second sheet is a formatted report that I'm creating to put that data into a different format for presenting to others. I'm using the Index and Match function along with range names. All is fine except where the string that should be returned is very long. When that happens, I get "######" all the way across the cell. It doesn't matter how wide I make the column or how tall I make the row, the "#s" do not go away. Is there a limit on the number of characters the Index function can return?
View 5 Replies
View Related
Sep 13, 2013
I have a google earth KML file that I have converted to text, and through a bunch of manipulation have been able to pull a series of GPS coordinate strings into a single cell string. Unfortunately, the string data is beyond what excel 2007 can handle for a single cell. so my thought is to have excel pull each coordinate string into a separate cell with which i can then run a macro to develop a new KML dynamically. (changing multiple attributes based on a query to a database)
Each string of coords actually maps out a single region (path) on the KML, truth be told it is telecom rate center data, and each rate center will have numerous other variables applied to it depending on my company's voice network capability for a given rate center. Currently my only desire is to depict differently any rate centers that I'm able to deliver VOIP services to by showing them in a different color...but these change very often so it will support to be able to auto generate the map from time to time.
the raw data from the KML looks like this:
Code:
CLINTONVL
RATE_CNTRCLINTONVL
STATEPA]]>
ff000000
1
0
1
[Code] ..........
After doing my data import, i extracted via various manipulations, the rate center name (a common lookup value that stays constant across multiple databases), and the string of coordinates. this is where i run into trouble. i need to pull each coord into a separate cell assuming i won't run out of cells in the x coordinates to gather this data OR find a way to grab the data via another lookup to another document...not desireable.
I want my output to look like:
Code:
RC_NAMECOORDSLATAVoip?
ALIQUIPPA
80.334114,40.520974,0-80.327493,40.520023,0-80.323915,40.51957,0-80.31997800000001,
40.519933,0-80.316958,40.521221,0-80.314431,40.521445,0-80.314262,40.521332,0-80.31398,
40.521194,0-80.31315600000001,40.520832,0-80.313057,40.520794,0-80.312805,40.520641,
[Code] ........
First off, my import was jacked up by missing some comma's...this i can fix easily with the string importation and manipulation HOWEVER...i still run into the issue of string length OFTEN.
View 1 Replies
View Related
