Substitution Of Specific Characters In Alphanumeric String
Jun 13, 2013
I have a large column of text with multiple entries similar to this: PC3L-10600R-9-11-A0. I need to replace the "11" with a 13. However I have other instances where the 11 appears (PC3-12800E-11-11-D0 or PC3L-12800R-11-11-A0).
I have found that I can use SUBSTITUTION
{=SUBSTITUTE(A50,H50,I50,1) and =SUBSTITUTE(A61,H61,I61,2)}
to handle the specific instances but I'd like to have a way to combine this type of function logically in one command.
View 9 Replies
ADVERTISEMENT
Aug 2, 2006
I am trying to remove all characters that aren't letters or numbers from a string. Is there any way to differentiate between a non-alphanumeric characters and alphanumeric characters? I'm thinking of something like "ISTEXT()" that I could use on one character at a time. Or are there any wildcards I could use in the Replace function?
View 9 Replies
View Related
Aug 2, 2006
I am trying to remove all characters that aren't letters or numbers from a string. Is there any way to differentiate between a non-alphanumeric characters and alphanumeric characters? I'm thinking of something like "ISTEXT()" that I could use on one character at a time. Or are there any wildcards I could use in the Replace function?
View 9 Replies
View Related
May 14, 2013
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.
Sub-base A 2-3
Sub-base B 3
Paving C 3-4
View 7 Replies
View Related
Sep 20, 2008
i have a macro that copies info from cell in row A and uses that info to name the whole row, now if the name has a space between the words it uses _ (underscore) substition so i don't get an error (when row name is defined - you can't use any special characters or spaces) i was wondering if anyone can help me to add "-" besides the space
here is the macro
Sub Macro1()
Dim a As Long
For a = 1 To Cells(Rows.Count, 1).End(xlUp).Row
Cells(a, 100) = Application.WorksheetFunction.Substitute(Cells(a, 1), " ", "_")
ActiveWorkbook.Names.Add Name:=Cells(a, 100), RefersToR1C1:="=Sheet1!R" & a
Next a
End Sub
View 9 Replies
View Related
Dec 22, 2013
I have the following type of info in A1,A2,A3...
Code:
nameGaryage40cityPittsburgheight190
age30height150
ameLindacityMichigan
citySan Jose
ameHarryheight180age50
My goal is to get as close as possible to this,so it will be easier to sort and manage
Code:
nameGaryage40cityPittsburgheight190
nameLindaage30cityMichiganheight150
nameHarryage50citySan Joseheight180
I can't use the "" sign as delimiter to separate them into different columns because the age,city,name and height fields are in random positions on different cells.The good thing is person's name will always come after "name" string, age is alwals followed by "age" string, so it cannot be like nameheight40Michigan180
I think the following would be the easiest method(not for me tho).If on B1 I had a formula that said "find the string "name" and write anything after it until you reach the next "" character".On C1 field I could have a formula "find the string "age" and write anything after it until you reach the next "" character.On D1 I would have the same for "height" string,then on E1 for city string.
My question is somewhat similar to this one
Extract A String Between Two Characters
Formula which outputs the data between 3rd and 4th instances of the "_" character.Can we substitute "3rd and 4th" with a specific strings like "age" or "height" ?
Code:
=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("|",SUBSTITUTE(A1,"_","|",3))+1,LEN(A1)),"_",REPT(" ",LEN(A1))),LEN(A1)))
View 3 Replies
View Related
Nov 24, 2006
I want to search a string for specific characters.
f.e. Begin = "bfPaa2"
I want to look for "P"
So, the answer has to be: Letter = "P" after searching the string
View 9 Replies
View Related
Sep 5, 2009
In one column I have different objects separated by a comma. I need to select one of these: 11,20,30,60,61 and copy it into another column. I have used this
For counter = 0 To not_empty_cells
For counter_dep = 1 To 5
position = InStr(1, (Cells(counter + 3, 4)), department(counter_dep))
If position > 0 Then
symbol_dep = Mid(Cells(counter + 3, 4), position, 2)
Cells(counter + 3, 10).Value = symbol_dep
End If
Next counter_dep
Next counter
It works, however, once in the first column there are the following objects: 60,6128,CZ, it takes 61 but it should take 60. Unfortunately, the position of the object can vary, it is not always on the first position.
View 9 Replies
View Related
Aug 14, 2014
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
View 7 Replies
View Related
Dec 3, 2012
I have alphanumeric characters and I have to rank them. Previously these characters were only digits (Using Rank function) and now some alphabets mixed with them. What is the function to rank Alphanumeric characters? They are like:
1A2240E01
210003004U
216000001U
499630001
463630001
400650001
View 3 Replies
View Related
May 5, 2009
I need a macro to delete only the alphabets which are entered along with numbers. Ex: 300ML. BY running a macro i need to delete only the "ML" and the number 300 should remain the same.
Range is For Rows: Entire rows
For columns: F TO P
The cell values betwwen these ranges contains alphanumeric such as 20KG,300ML,200M ...etc
Hence i need a macro to delete the alpha charecters for the particular range.
View 6 Replies
View Related
Jan 7, 2007
Is there a way of extracting all non alphanumeric characters from a single collumn?
After extraction, I just want strings with A to Z letters, 0 to 9 numbers, spaces and hiffens(-). All in lower case.
View 9 Replies
View Related
Mar 9, 2008
I am using the Concatenate() function to join strings from A1 and B1.
The result goes into C1.
How do I remove all the punctuation too?
In other words, I want to keep ONLY the upper and lower case alphanumeric characters....
View 6 Replies
View Related
Jun 12, 2008
I have the following in Column A
28VNC
JR2KL
29C9O
PQS11
I'd like have this result
28
2
299
11
How would I achieve this.
View 6 Replies
View Related
Aug 2, 2012
I'm trying to sort by a column with one to four digit numbers, any of which may be followed by a single letter, i.e. 1,2,3 4A,5, 10,11A,75,101A,600,705,1010B,1011A.
I'd like them to sort in the above order. I am only able to have excel 2003 sort all of the numeric cells first, followed by the cells with the alpha character.
View 4 Replies
View Related
Jul 20, 2009
I have a rather difficult task to accomplish. What I need is to be able to extract an alphanumeric value from the string. It is a serial number of the equipment and it is always nine alphanumeric characters long. The problem is it can be anywhere in string. Example: “2156545 36 month lease NWL023568 Nancy Clay”. The serial number I need to extract is NWL023568.
View 7 Replies
View Related
Aug 28, 2012
I have values in cell like,
1,2,3 & 4
10,11
8,9,10,11,12
etc.
the formula to get min and max from this string
for the first value Min = 1 and max = 4,
for the 2nd value Min = 10, and max = 11
fo the 3rd one Min = 8 and max = 12
View 3 Replies
View Related
Apr 8, 2013
Objective: To create a list of numbers based a cell which are present in an alpha-numeric string
Issues: "-" shows up at times without the context of numbers (i.e. MS-DRG)
Issues: I am not interested in float numbers (i.e. 37.63-37.66)
'Example data in cells (row 1 = A1, row 2 =A2, row 3 = A3, row 4 = A4)
MS-DRGs 767-768, 774-775
Rev 115, 125, 135, 145, 155, 655-656
Rev 174
MS-DRG 001 with ICD-9 37.63-37.66, 37.52
'Desired Results (row 1 = B1, row 2 =B2, row 3 = B3, row 4 = B4)
767,767,768,774,775
115, 125, 135, 145, 155, 655, 656
174
001
View 9 Replies
View Related
Jan 9, 2007
I am looking for formulae to identify the first 20 non-zero numbers from a string of alphanumerical and non alphanumeric characters
EG If column A any data such as:
123
0123
01(2)3
0.123
123abc
0123abc
01(2)3abc
0.123abc
a1b2c3
1a2b3c
Then the corresponding row for column B would return 123
Using help from my maths Prof and some IT bods, i have so-far come up with the array-formula:
=1*MID(A7,MATCH(FALSE,ISERROR(1/MID(A7,ROW(1:100),1)),0),20)
assuming that the total string is not more than 100 characters.
View 7 Replies
View Related
Jun 2, 2014
make equation to remove alphabet characters from a text mixed with alphanumeric characters like below.
eg.
1.ASD000234234-1RST to 00234234-1
2.ZYXW2343WE to 2343
3.abcde2023820eiwls to 2023820
like this
View 3 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
Jan 11, 2013
I have a list (SIC Codes) and I want to remove the numbers. The numbers range from 2 to 8 deep. The list exists in column B and I want the new list in column C.
01 Agricultural Production Crops
011 Cash Grains
0111 Wheat
0112 Rice
0115 Corn
0116 Soybeans
0119 Cash grains, nec
011901 Pea and bean farms (legumes)
01190101 Bean (dry field and seed) farm
01190102 Cowpea farm
01190103 Lentil farm
01190104 Mustard seed farm
View 3 Replies
View Related
Dec 16, 2008
I would like to parse a part number.
ab24s-12-g4a
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
View 5 Replies
View Related
Oct 11, 2011
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:
AB23400 12 -> 2
CET25800 1 -> 2
NYT67300 H01 -> 6
HSRF49900 1 -> 4
View 9 Replies
View Related
Jan 3, 2013
I have a string that with spaces in between.
example:
X3PP2 1200 U
I would like to extract the "1200". How can i do this in VBA?
View 4 Replies
View Related
Dec 4, 2008
Anyone know how to do this?
View 9 Replies
View Related
Aug 23, 2008
I have list of 15000 of Alphanumeric data for ex. ADEDO125ADSD589ADF121,UIEIROIWS12556ERE545,ADAS15455212AD4564AD2
And so on. Now I want to extract word from this alphanumeric. And i want to use only formula not MACRO OR VBA
View 4 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
Dec 31, 2007
I have created a macro that searches for an existing part in my worksheet and copies the found part to the row above. I would like to increment the value of cells B and H in the new copied row. Both cells contain an alphanumeric string that ends with a numeric value. For example cell B has a string like this "APL-DK0030" and cell H has a string like this "Dell Optiplex GX260 V09"....
View 9 Replies
View Related
Jan 20, 2008
What is the formula to be used to extract number from a alphanumeric string located at different row?
example:
test123test128test131
test124test129test132
test125test130test133
test126test131test134
test127test132test135
test128test133test136
result: ....
View 11 Replies
View Related
