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.
if there is a "-#-" in the string, that is the value I want. If the "-#-" is not present, I would like to take the first number. Currently, as written in your first example, I am getting the first number - excellent and thank you!
In the sku, the first digit reprepresents capacity. If a second number is present after the capacity portion of the sku (i.e. "-#-"), that means that they want to order it filled with less than capacity. For instance, a carton of eggs and they want 6 eggs instead of the max capacity of 12. (i.e. egg12-6-z2z). Numbers after the second octet (i.e. z2z) do not matter.
It would be a number only in the second octet. If there are characters with it, the equation should disregard it.
Here are the scenarios, and what the formula should accomplish
1) egg12 --> currently returns 12 2) egg12-6 --> would return 6 3) egg12-6-g2c --> would return 6 4) egg12-g2c --> would return 12
Have many rows of alphanumeric strings. 2-4 letters followed by a variety of numbers, some spaces, and some additional letters. What I need is to return the first number after the initial 2-4 letters, everything after that first digit is junk. Thus, I would like a formula that takes me from:
Having trouble with this one. Searches seem to bring up every other variation of extracting info from strings except this.
I have cells which contain alphanumeric strings as below and, using a formula, I want to extract everything from and including the last letter to give A 2-3, B 3 and C 3-4 in the examples below.
I have a set of task descriptions that I am attempting to trend on. Some of these (Column B) have the customer's name added to the description; others do not. I need to be able to make a list of task names (ColumnA), removing the name from the text string.
The formula I am using is [=LEFT(B3,FIND("for",B3)-2)].
The problem I am having is when the description does not contain the "for" built into the formula, I get "VALUE" error. What adjustment can I make to the formula to pull over the Description if the "for" does not appear in the text string?
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.
I need to be able extract a string from within a longer string. The information I want will always start 5 chracters in and be bordered by an underscore either side, but could be of any length. For example in the following string, I would want to extract WF602.
I've been looking at the RegExp functions but can envisage problems with this as later parts of the string (WR34) have the same form as the part I need.
Is there a relatively simple excel function which will extract the end of a text string. The end has two or three full stops / periods so I would like to count back three to six or more characters and use the full stops as 'cut off points' by passing other full stops?
Wondering if it is possible to generate a random 4 digit number based off an alphanumeric string?
Example;
Cell A1 has 123XVF1234 Cell A2 has 321AFW4321
In B1 I would like to have a 4-6 digit number that is generated based on the alphanumeric data in Cell A1 (and so on down the list). If that is possible, I would also need to be able to convert back the 4-6 digit number back to its original alphanumeric value
Example;
If B1 returns 643562 it would need to be able to be converted back to 123XVF1234
Below is a sample of data I need to extract the 8 digit number:
WO_32092_56228491_575482-113476-WP55 WO_32092_56228491_115130-WP55 WO_32092_56228491_115116-115118/115117-WP55 WO_32092_56228491_291881-318903-WP55 WO_259_56748761 WO_13895_52245652COUNTYRDN-30 WO_17368_51421730-A-ADDPOLEINFIELD WO_17368_50885431-A-ADDMISSINGPOLE WO_11021_52350485brock3377 In a spreadsheet, I had set up three columns where:
A: removed first three characters. WO_32092_56228491_115130-WP55 to 32092_56228491_115130-WP55 B: removed up to the _ . 32092_56228491_115130-WP55 to 56228491_115130-WP55 C: captured the first 8 characters left. 56228491_115130-WP55 to 56228491
I am pulling the data into Excel via an ODBC where there is thousands of rows of data. The three column process puts a tremendous strain on the processor.
Is there a formula that will extract the 8 character number without a three step process?
I have several lines with text strings containting three numbers in each line. I need a code to extract all three numbers from each text string. The numbers can be placed on the following columns in each row.
I have a text strings that contains the word "CHQ 123456" or some other CHQ number. There might be another word/words after the word CHQ xxxxxx something like:
Payment by CHQ 123456 against your inv 45225 ABCD Company Limited - CHQ 187546 PO 4520061257/CHQ 745865/invoice Number 4125
I need a UDF that extracts the CHQ No. from the text string Eg:-
i have used the RIGHT function to remove the 1L/1S and the 1L from the samples belows
the problem i have is when the 1L is in its cell there are 3 spaces before it how can i remove those spaces so that all the data is to the left of the cell
i have a data as above, i wanna to extract the 250ml from the column 1, only 250ml (basically i want to extract the size code like 150g, 100gm from the data above).
I am trying to extract numbers after a specific text in a text string, for eg :abc SN 12345 xyzedf SN No. 456 mnoAs per above, i want to extract any numbers afters "SN". the numbers can be vary in digits i.e. it can be 3 digit numbers or 4 or 6. Also, at times there is some other text in between (like SN No.) numbers and search word (i.e. SN)Any formula to get result as "12345" and "456".
/s will always be present and will always be followed by one or more digits (no limit).
/c may or may not be present but if it is present, again it will always be followed by one or more digits (no limit)
I have been using TextToColumns with "/" as the delimiter and it works fine in most cases. However, the data frequently contains additional "/" characters which breaks everything. some examples of valid but problematic data are:
I have a worksheet with over 10,000 records. The column that lists where a person is willing to relocate can have up to 60 city/state entries in one cell.
Here is an example of what appears in one cell - this is exactly how it appears:
ASAI Los Angeles (XX , CA DFO Pacific (XX ONLY), CA DFO Pacific Area Analyst Laguna Niguel (XX ONLY), CA SAI Los Angeles (XX ONLY), CA Ldr Los Angeles El Segundo POD (XX ONLY), CA Ldr Los Angeles Long Beach POD (XX ONLY), CA Ldr Los Angeles POD (XX ONLY), CA Senior Ldr (XXXX) Washington (XX ONLY), DC
What I need to do is be able to sort on city and state, so I wanted to be able to extract and separate the city and state. I tried using a find/replace (CTRL J) to enter a semicolon between each entry and thought I could do text to columns to separate, but that doesn't work.
How I could extract this information? Notice that the first entry is missing ) - that is throughout the records.
