Stripping Text From AlphaNumeric Strings
Aug 29, 2006
I'm working on an attendance spreadsheet that shows the employee's absence using the format "X####", where X can be one of the following three values: S(for sick time), P(for personal time), and V(for vacation); "####" is the number of hours absent (can be from 0.25 to 8.0,measured in 15-minute increments, # can be either a digit from 0 to 9 or a decimal point, and there can be from 1 to 4 characters, but must be >0(I'm not worried about data validation yet).Examples are P8.0, V.5 (V0.5), S2.75,etc.
The spreadsheet is laid out such that each row represents an employee, and each column is a workday. After the employee's information are three columns labeled P,V, S. I'm trying to generate a formula that when applied to an employee's row, will display a running total of the absent hours based upon personal, vacation, or sick time taken. We'll call the range "CAL"(Short for calender)
To strip out the numeric characters on the right, I use the formula RIGHT(CAL, (LEN(CAL)-1)). To Determine which column the data should be summed in, I use the formula LEFT(CAL,1). Blank Cells are permitted, in fact most of the row will be blank. Now that I've figured out how to strip out the alpha from the numeric, how can I get the totals? I apologize for writing "War and Peace," but I wanted to be as concise as possible-
View 5 Replies
ADVERTISEMENT
Feb 8, 2007
On a work sheet I have 20 columns that may or may not have data in it. In the 21 column I want to have a sum "of the values" in the other columns. Problem is the data is in this form i.e. 6 01/17/07 smr or 15 1/19/07 gtw or 24 01/21/07.
To clarify each cell will have a value of a number from 1 to 99 followed by 2 spaces. Then it can have 2 more spaces and an id tag or just finish with the date.
So, from the example above in column 21 I would have a current value of 45 and it could change if more values were added.
Note values are generated from a macro. It may be possible to change the macro so that a comma or such may be used to seperate the different "values / data types"
View 4 Replies
View Related
Oct 28, 2009
I have a column of one to three digit numbers (I5:I34), some of which contain an asterisk after them. In I35 i want a formula to find the highest value from that column. Additionally, if two numbers in the column are the same value, but one contains an asterisk after it, it should use that value in I35, instead of the one without the asterisk.
View 6 Replies
View Related
Feb 27, 2014
I am working on a report but I need to split of text cells that contain text and numbers. I had worked in some formulas and I got stuck again when I drag down the formulas. In the attached file, you will see two tables. The one in the left is my current job. In the table of the right is how the table must look like.
The table is complete and will give you the whole idea.
Basically, I need to split the cell into three categories that are "Family", "Model" and "Phase". In example:
Cell Family Model Phase
CCA CCA
DC50X DC50X
DCX3300CRDDCX3300CRD
DPC2434 DPC2434
DCT-1700DCT1700
DCT-1800DCT1800
DCT-1800P3DCT1800P3
DCT-1800P4DCT1800P4
Attached File : Family Model.xlsx‎
View 4 Replies
View Related
Mar 7, 2009
I have this formula that extracts numbers from alphanumeric strings.
{=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$100),1)),0),COUNT(1*MID(A1,ROW($1:$100),1)))}
However this extracts only the 1st instance of the numbers
In a string like 123avfbsdf4556.. it'll extract only 123.
My questions are the following:
1. Is there a way that i could get the result as 1234556
2. A way which refers to a cell where I put in a number and it'll extract those many number instances. In the above example, if I put the number as 1, it'll extract 123. If I put the number as 2, it'll extract 4556 and so on.
I guess this would require some modifications to the Match function so that it does not look at only the 1st instance.
View 9 Replies
View Related
Jan 25, 2010
I have in two cells :
a1 b1
1m 1-3-6-11-17
how can I get:
1m1
1m3
1m6
1m11
1m17
using vba code.
View 8 Replies
View Related
Nov 17, 2009
I need to sum the numeric portions of any cell containing a certain letter within a row. I found a solution that works if all the cells within my row are either blank or contain a string with the "desired letter" lets say the letter is "a" so that we can compare it to ....
View 6 Replies
View Related
Apr 6, 2008
I have a column with product packaging sizes with different syntaxes, such as:
"2 x 5 kg"
"200 l (212 kg)"
"1000 l (1400 kg)"
"5 kg"
"20 l (20,54 kg)"
As you can see the syntax varies a lot! Now I need a formula to extract certain numbers for different calculations. E.g. if the string has a letter "x" then the number(s) in front of it would be extracted. Or if the string has the letter "l" or "kg" the numbers in front of them (including decimals) would be extracted while discarding the rest. Is this even possible? Personally, I would change the whole system and break the strings across a number of columns but unfortunately it is not up to me...
View 4 Replies
View Related
Mar 5, 2009
I generated my urls to online photos, I referenced cells where some are only numerical (ex. 479) while others contain a numerical/text mix (ex. 3014-RACK). Here is my url code in excel...
View 10 Replies
View Related
Jan 22, 2007
I have a range that can have numbers and text in it. I want to strip out anything before and after the number. For example. If any of the cells contain text such as "The price is $4.28 per pound" it would strip out everything except the 4.28. This cell may also contain Japanese yen so you can not use the decimal for reference.
View 14 Replies
View Related
Feb 8, 2007
I have a text file that has been saved via PL/ SQL code from a CLOB (due to the MSDAORA not supporting CLOBs). Unfortunately, due to passing the file between operating systems it appears that the CR and LF characters are not consistent. This means that my third party application in Excel cannot read the text file properly.
View 2 Replies
View Related
Apr 11, 2006
I have a column that's supposed to be an address, but somehow got the
company name mixed in at the beginning:
123 Main Street
One Park Place
Acme Company, Inc. 456 Easy Street
Alpha-Omega Dry Cleaning Four-Twenty Highway One
I can use the formula
=RIGHT(A1,(LEN(A1)-((MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")-1)))))
for stripping the text before an actual number
Acme Company, Inc. 456 Easy Street
to
456 Easy Street
BUT - I'm not sure how to strip before a text representation of a
number (one, two, three, etc)
Alpha-Omega Dry Cleaning Four-Twenty Highway One
to
Four-Twenty Highway One
View 10 Replies
View Related
Aug 16, 2008
Is there a way either by VBA or manually (preferably both, if possible) to actually unite the X amount of numbers that are in a cell given the contents is alphanumeric? I'll give you the following examples to see if you can understand what I' referring to?
DATA output should be
asd67,h876 --------> 67876
2,3,ujdj5&34 -------> 23534
909k86m34 --------> 9098634
View 14 Replies
View Related
Aug 15, 2008
I import data from another program in order to evaluate it. Unfortunately, one of the fields I need contains copyright data, however, it has been very inconsistently entered into the database. For example, sometimes the data appears "c1999." or "-1999" or "" or "[1999]" or even "19?" and also sometimes "1999, 1990" and many other variations on that. I discovered the link in the excel help file about extracting numbers from alphanumeric strings, but my situation is still too variable for it to apply; that file didn't take into account that alphanumeric strings don't always lump numbers and letters together. I was able to correct a few things, but my command of excel isn't knowledgeable enough to really come up with something effective.
Some ideas I had that I don't know how to implement: is there a way to strip non-numerical characters from an alphanumeric string? (I've been doing some find/replaces to get rid of some of it, but that is obviously not very efficient when I have to repeat this process daily.) Perhaps then I could just detect the first 4 numbers of the string somehow. However, that doesn't solve the problem of when a wild card is used as in "199?" or "20?" etc.
Bottom line, I just need to grab the first four numbers that appear in the string (but NOT additional numbers that occur after a wild card or a space if the year was not completed in 4 numbers; in that case I'd just be happy with a null value).
I've been doing this with a formula so far. My only experience with macros has been in simply recording them, not actually writing them, but I'll give anything a try.
View 9 Replies
View Related
Feb 28, 2013
how to Chk the text string in particular cell, compare it with a super set column and get the full from of the text string from another corresponsing column and the output will be corresponsing full form of the chked text string?
View 6 Replies
View Related
Mar 8, 2013
How do I use an Excel formula to find which (if any) multiple sets, each of up to 50 words, exist in a series of rows of a spreadsheet - if set A has one or more words found in a searched cell.
A positive result will return a specific value in the designated result cell. If none of the words in Set A is found in the searched cell, the formulae will repeat the test for the words in Set B, and so on.
After all 50 sets of words have been tested, the formula will move to the next cell in the searched column.
New words will be added to the sets of words continually as required.
Multiple words within sets are included in double quotes. Within each set of words there will be some n-tuples of words (i.e. 24 adjacent words) that contain one or more of the words in the set, but for which the formula will be required to return a negative result. Example: Set A = word 1, word2, word 3, "word1 word2 word3". (The words within a set could also be each entered in separate columns, as opposed to all included in a single cell.) The single column of text to be searched is about 10,000 rows.
I am wanting to use the above in a spreadsheet that contains data downloaded from a series of bank accounts to automatically allocate items of expenditure to one of 20 or so different categories of expenditure.
The formula will search the description field to find words that are used in the in the downloaded files from the various accounts to describe each transaction.
If a word describing travel expenditure (e.g. hotel, "holiday inn" but not "holiday travel") is found in the description of an expenditure item - the item cost will be allocated to the TRAVEL EXPENDITURE column, which is one of 20 or so different categories of expenditure.
Happy to consider a different solution if the task can be done better a different way.
Tried using a combination of INDEX/SEARCH/IF in Excel, but was not able to get a correct result. PS I am using Excel 2011 for Mac - which does not allow macros, so the solution needs to be entirely formula based.
View 1 Replies
View Related
Sep 27, 2006
I try to write the function that gets some "ref" and returns appropriate item.
For example: if I give to function "C3", it returns "AAA"
if I give to function "R18", it returns "BBB" (cause it between R15 to R26)
if I give to function "R9", it returns "BBB" also.
View 9 Replies
View Related
Sep 4, 2009
An original text string entry appearing in an Excel cell would be:
"N7C Neuroprotective J5Z Antiviral, other M2Z Antiarthritic, other J5A Antiviral, anti-HIV"
I need to extract N7C, J5Z, M2Z and J5A from this string and list these alphanumeric values in separate cells adjacent to the original text string. The challenge is that these alphanumeric references may appear in different positions within the original string with no fixed value e.g. a "," separating them. The alphanumeric references may also be 3 or 4 characters in length and there may be different numbers of alphanumeric references in the original string.
Another example would be (very different from the first):
"T2Z Recombinant, other K5B Radio-chemoprotective J3Z Antibacterial, other D3A Vulnerary A10C Symptomatic antidiabetic K6Z Anticancer, other R8A Antiasthma B6A Septic shock treatment I1Z Immunostimulant, other S1Z Ophthalmological R8B Antiallergic, non-asthma M1A1 Anti-inflammatory"
You can see that in this further example "A10C" & "M1A1" are 4 character alphanumeric strings wheras the others feature 3 characters.
View 9 Replies
View Related
Jun 23, 2009
I need to automatically populate a column with an alphanumeric MFR0001 to MFR9999 and am currently using the formula below to add 1 to the start point (A1)...
A1=MFR0001
Formula in A2 =LEFT(C1,3)&(RIGHT(C1,4)+1)
This works perfectly if I start at MFR1000, but breaks and drops the 000 if I use it on the number I need to start with (MFR0001).
View 3 Replies
View Related
Dec 7, 2012
I have a large .txt file that I would like to edit using VBA. What I would like to do is to find each of the following text strings in the text file :
De:
De :
Envoye
A:
Objet:
If the character right before those text strings is not a carriage return (chr(13) then add a carriage return.
I would like the code to read each line in the text file and execute the task mentioned above.
View 9 Replies
View Related
Apr 23, 2007
What I have is a column of data(text) which contains amongst all the text three strings of text in ever cell in the column which I require copying into three adjoining cells
The data I require is :-
(a) The persons name which is always after the word ‘Requester’ e.g. Requester Steve Robinson
(b) Their office location which is directly after the persons name and is in brackets e.g. (Newcastle User)
(c) The Approving persons name which is preceded by ‘Approved by’ e.g. Approved by Christine Hunting
See examples 1 & 2 below
Example 1
CR0/CRZ3651 Requestor Steve Robinson (Newcastle User) Tel: 01234 798157 Approved by Christine Hunting
Please install and configure 2 Ultra 2s (typhoon and lancaster) for use as ARTE workstations. These workstations require Solaris 2.5.1 plus the same patches as before
Example 2
CR0/CRZ3118 Requestor Doug Cunningham (Newport User) Tel: 0114 9881480 Approved by John Smithers
Please provide support to set up Cisco 2691 Router and PIX-506E Firewall to enable external connection of a remote terminal for project work.
As you will appreciate the text in the cells is of non standard lenght and the three pieces of information can be located virtually any where in the text
View 9 Replies
View Related
Dec 26, 2013
I have the following text string.
What formula can I use to extract these results on the right?
Sample file.xlsx
View 8 Replies
View Related
Oct 23, 2009
I've got a cell with codes, such as:
21ML,
43GKP,
etc.
I want these to look like :
ML21,
GKP43,
i.e. swap the numbers with the letters but keeping the order of the characters.
View 6 Replies
View Related
Oct 2, 2012
I have data on two spread sheets which I am trying to cross reference. On the 1st spread sheet I have a list of product codes e.g. 22886, On the 2nd Spread sheet I have a list of product titles which all include product codes within the text e.g. CHRISTMAS FANCY DRESS COSTUME - SANTA SPARKLE OUTFIT 22886 M
I need to insert a formula into the 1st spread sheet which will tell me if based on the product code it is present in the 2nd spread sheet. I have tried VLOOKUP and I think because I am trying to search for a code within a text string it keeps failing! All cells are general cells but the position of the code within the titles varies.
View 7 Replies
View Related
Dec 6, 2006
Take for example ABC10 in Cell A1, ABC199 in cell A2 and ABC9 in cell A3
How am i able to sort such that ABC9 appears in cell A1 followed by ABC10 in cell A2 and ABC199 in cell A3?
View 9 Replies
View Related
Jul 16, 2007
I need to extract just the text from cells which contain both letters and numbers. There can be one or two letters, followed by one, two or three numbers. (For info, these are chemical elements and their masses). So for instance,a cell could contain anything from the following B9, B11, S32, Ca44, Mo100, I129, Th226, U238 etc etc. I know I've seen this done before, but I can't find it now. It also has to be formula based, not using VBA.
View 4 Replies
View Related
Aug 30, 2007
The title of this thread is the best way I can describe what I wish to accomplish. You see, I have a column of information requests in an Excel spreadsheet, each of which his tagged with an alphanumeric request number, and I wish to sort and/or perform and min/max function on the request identification. However, each ID has a IDR in front of it - for instance, IDR000452 - and the letters make the system read the contents as text. Does anyone know of a formula I can use to create a new cell which contains only the numerical contents of the above example?
View 4 Replies
View Related
Oct 15, 2007
We use excel to organize salaries and contracts for my fantasy baseball league. I need an easy way to go through all the teams and subtract one year from each players contract. This is an example of a team.
The contracts are in the columns like this NG-3
I would like to change that to NG-2
View 9 Replies
View Related
Feb 19, 2008
I'm looking for a way to extract numbers from a string.
The string can potentially have as few as one number or as many as 25. Each number is separated by a comma.
Here is the code that creates the string: ...
View 9 Replies
View Related
Apr 4, 2008
Is it possible to increment / decrement cells using wild characters? I've found myself needing to increment values at different position, for e.g. :
ABC-###-FR05-AB
ABC-222-FR##-AB
C##445-KJ
where I would want to be incrementing # placeholders. It would help if I can give it a start number, and the increment amount (which can be different than 1).
What about possibly extending it a bit more to include two or more place holders at the same time as follows:
ABC##- FR*** (where # would have its own starting number and increment value, and * would have its own).
View 9 Replies
View Related