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 RepliesI 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 RelatedI 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.
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
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
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
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
I'm trying to remove everything after a specific character in a string.
I.e. change a website address to the hostname
http://www.excelforum.com/newthread.php
http://usa.excelforum.com/forum/new
to
excelforum.com
usa.excelforum.com
I'm using this formula, which strips the http:// and the www., but does not replace the characters after the first remaining "/" as the wildcard is not recognized.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"www.",""), A2,"http://",""), A2, "/*", "")
I have a string like: AAJDGYE030000460. How can I remove the first character in a macro? I need to look at the second,third, and forth character
View 3 Replies View RelatedI have a set of data in column a that consists of email addresses. These email addresses all have underscores after them, ie "abc@hotmail.com_______". It will be a different amount of underscores everytime and I don't want underscores to be removed that are actually part of the address. I had been using the find replace function through vba, ie
Range("A:A").Select
Selection.Replace What:="_", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
however this ofcourse removes from actual parts of the email address. Is there a way to do this?
I've come across multiple times where I have to do this same sort of task, and I don't think I am doing it the most efficient way.
What I need to do is take a variable which holds a string and remove the last 9 characters from it. Don't need to know what the last 9 characters are, all I care about is knowing what the other characters are in the string. I know this can be done through thingslike susbstrings, but I don't think VBA has a substring function.
A string contain a number like AB12345 or B7845 How could I remove the alphabetic characters and keep only the number.
View 2 Replies View RelatedWondering 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
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
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.
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.
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....
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.
I have found a very useful UDF for removing non-alpha characters from strings. (See below, Credit for posting to Stanley D Grom - Ozgrid post ´Removing Non-alpha Characters From Text´).
Option Explicit
Private Function RemoveCharacters(InString As String) As String
Dim intLoopCounter As Integer
Dim intStringLength As Integer
Dim intASCIIVal As Integer
intStringLength = Len(InString)
InString = LCase(InString)
For intLoopCounter = 1 To intStringLength
intASCIIVal = Asc(Mid(InString, intLoopCounter, 1))
If intASCIIVal >= 97 And intASCIIVal <= 122 Then
RemoveCharacters = RemoveCharacters + Mid(InString, intLoopCounter, 1)
End If
Next intLoopCounter
End Function
Two requests:
1. Could the UDF be modified such that any part of a string contained within brackets is also removed (e.g. "NLGA High Street (West-Enfield), EN6" becomes "nlgahighstreeten")?
2. Can an argument be added to the format of the UDF, such that numbers (0 to 9) are either included or excluded (e.g. RemoveCharacters(A1,1) where the argument ´1´ would include any numbers (0 to 9), so "NLGA2003 High Street (West-Enfield), EN6" becomes "nlga2003highstreeten6")? ´blank´or ´0´would exclude these numbers, i.e. would return "nlgahighstreeten"
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.
I have about 10000 cells of varying length in text and numbers but they all end with numbers on the ends of them. Using text to columns doesn't work because they're all different lengths and it cuts them off in the wrong place.
Is there a formula I can use that will return only the text from these cells and remove the numbers. It would definitely save me time from individually deleting the numbers at the end.
In cell A1 i have the value ABC123.
I want to remove the 123.
Can you tell me how to do this?
I am looking to remove numbers and symbols from a single cell.
For example, I start with something like:
Cell A1: M. Flank1, T.D. Chang1*, T. Person2, J. Doe3
Cell A2: T.R. Money1*, Y.R. You2
...
and I want:
Cell B1: M. Flank, T.D. Chang, T. Person, J. Doe
Cell B2: T.R. Money, Y.R. You
...
And, there are about 300 more, with varying number of people per cell.
I'm not great with Macros, so if there is a way without macros, that would be good, but, if a macro works best, I can give it a shot..
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 RelatedI 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
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
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.
I have the following text string.
What formula can I use to extract these results on the right?
Sample file.xlsx
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
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