To extract number from string in Excel, it'd take a little ingenuity, a bit of patience, and a bunch of different functions nested into each other. Or, you can run the Extract tool and have the job done with a mouse click. Continue reading
Comments on: How to extract number from string in Excel
by Svetlana Cheusheva, updated on
Comments page 12. Total comments: 577
Hello. What if I have a specific number, for example 1231, but that number is obtained by a sum of 3 numbers? Can I extract numbers from that sum formula?
FYI, this is way more complicated than necessary. In a column to the right of the list, I pulled out one of the numbersets manually and Excel auto-filled the rest. My numbers were surrounded by text on both sides and of varying length.
Christopher:
There are two instances of "07" in this sample string.
If you want the first instance and the next 9 digits then where the string is in C13 enter this in and empty cell: =LEFT(C13,11)
If you want the second instance and the next 9 digits then enter this in an empty cell: =RIGHT(C13,11)
Hi guys,
I have a number string for example like "07427640900247247520603080251507427640900" and I want to extract a 11 digit number starting with 07 from within this number string. How can you do it?
Hi
I have data like:
sodium fluoride 1.1 mg O 0.5 mg fluoride
benzydamine
magnesium hydroxide 3 g O
how can extract sodium fluoride, benzydamine,magnesium hydroxide to column b
and 1.1, 3 to column b and mg, g to column c and o in column d
Regards
Majid
Dear,
a1 a2
I have BXC123644 i want rezultate BXC1 all text and one number
AEB56984fg5 AEB5
CT12564984 CT1
CDTMS56DGT CDTMS5
Hi Guys - i want to extract the 2018 from the below text string in cell A3, can someone help? TES1-TEST-Bathtimes-2018, bloggs, joe
Dear,
do appreciate your guidance how to extract number with decimal from excel?
for example |1234.56 USD| to |1234.56|
Regards,
Ronald
we have a data like this:-
r0fsd9958405019e34 Required Data : 9958405019
5353w9810105370qw4354 : 9810105370
ewrew8860339000dfdf : 8860339000
erfsd9873903709sds4
ewrew9810241172-35
edsd9582121827dfd35
rdf9999377066dfs5fd
wer9873744954df43
53sdf9818803734adf443
I want to extract only mobile number which is 10 digit at each place, please let me know the easiest formula to extract these mobile number in simple way.
Regards
Sachin
Hello, Sachin,
If we understand your task correctly, you may find our Extract Text add-in helpful. Please try to use the "Extract by position" option to extract the 10-digit mobile numbers from your cells. Just enter "6" as the position number of the first character, set "10" as the number of characters to extract and click "Insert Results".
Mary, can you share the exact formula as the example of Sachin question. I also want to know how to split the number from text following Sachin's query. Thanks in advance!
Hello, Nay,
As you can see from my comment above, I recommended to use our Extract Text add-in to solve Sachin’s task.
Can also use the simple formula as below
=MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),LEN(A2))
Then hit Ctrl+Shift+Enter
Another way to work around it.
=RIGHT(A2,LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},""))+1)
It's great! but shamelessly the formulas in english doesn't work in german... So, thanks anyway! :D
Please Help!
I have a text string containing comma delimited numbers in a single cell, e.g. "2, 3, 4, 10, 11". I am looking for a formula that looks for a given number and returns TRUE if it is contained in the string or FALSE if it is not. The formula must not confuse numbers with characters, e.g. looking for "1" in the example string above must return FALSE.
Any help much appreciated!
Type of Issue: Booking failure (order in entered status)
Here as detailed description of issue as possible.: Order 787895 is stucked can you book that. Thank you!
Order number: 787895
Priority: High
Provsiioning bars are in N/A status. Plea
----- Please help me to write excel formulate to get order numbers
Hi!
I am having trouble with my formula, despite following the instructions step by step!
I want to add numbers associated with different words. Example:
15 walk
60 gym
10 run
10 walk
30 run
I want the sum... Walk= 25 Gym=60 Run=40
To rephrase, I want the sum of all digits associated with "walk" OR "gym" OR "run".
I can't figure this one out! Thank you for any help you're able to provide!
-Stephanie
Stephanie:
Enter "Run", "Walk" and "Gym" in cells C62, D62 and E62 respectively. These will be the headers.
Enter the data in A48:A57. The formula is case sensitive so be sure the data matches the caps in the headers.
In C63 enter =SUM(IF(ISNUMBER(FIND(C62,$A$48:$A$57)),VALUE(LEFT($A$48:$A$57,FIND(C62,$A$48:$A$57)-1)),0))
then with the cursor in the formula bar in the formula click the CTRL Shift Enter keys at the same time. This is an array formula so you need to tell Excel to evaluate it as an array. When you enter the formula and then in the formula bar you put the cursor in the formula and click the CTL SHIFT ENTER keys it will put curly brackets around the formula which indicates to Excel that this is an array.
When the value appears in E63 copy the formula over to D63 and E63.
As you enter more data in the A range be sure to change the second cell address to match the last cell in the range. Right now the range is A48 to A57. If you add more data change the A57 to another cell address. Remember, there are three places in the formula for that range.
Stephanie:
I should have written:
"When the value appears in C63 copy the formula over to D63 and E63."
Hiren:
Because each of the substrings you want to are different and the strings vary in length, I think the best you can do is to use different formulas for each.
For example, with the string "XYZ 50MG TABLET" you want the "50MG" substring. You can use =MID(H35,SEARCH("MG",H35)-2,5). With the "PARADICLO 500/100MG TABLET" you can use =MID(I35,SEARCH("MG",I35)-7,9) to extract "500/100MG". This approach can be repeated for each string and substring. It's a little klunky, but I don't see anything other than the "MG" that's common to the strings.
Hi Doug,
Thanks for your response. Yes, you are right. I had already tried this and helped me in few cases. I think, for rest of the things, I will have to do manually as the description of products are different.
Once again Thanks.
Regards,
HIren
Hello,
Please help me in following.
I have a bunch of data containing product name with its strength in "MG", Like XYZ 50MG TABLET, PARADICLO 500/100MG TABLET, ABC 100 MG CAPSULE like wise....
I just need to extract "STRENGTH" like, 50MG, 500/100MG, 100MG, 0.375/0.5MG likewise.
Please help, if anyone knows.
Hiren
9898132180
Hi, I need help in extracting numbers from a string but along with spaces after each set, for ex:
A1 = first number48832234//second number552437548
Result = 48832234 552437548
i am already aware of the formula to extract all numbers.
any help is highly appreciated.
I am awaiting some help ... is someone there who can help ....
Nadeem:
I remember this question from the other day and it isn't clear. However, if you have two numbers in one cell separated by "//" and you want to remove the "//" and substitute a space then this works:
=SUBSTITUTE(A1, "//", " ")
Hi,
I am attempting to return only those numbers below which contain "50097" in the first part (before the hyphen) which are pre-fixed by "BA". The numbers need to be returned in the same cell.
BA50097-52201 BA50097-63623 BA50097-64930 BA52201-50097 BA56510-50097 BA63623-50097 BA64930-50097
I've been racking my brains. Any help would be appreciated.
Thanks,
Andrew
For clarity, I am attempting to return the second part of the string (after the hyphen) if 50097 is a match in the first part. Hope this makes sense.
I want to extract 541 from KA54115259.
Jessica,
you can use MID function in excel. It returns a specific number of characters from a text string starting at the position you specify.
in you case KA54115259 is the text string. To extract 541 from this string use =+MID(cell number,3,3). cell number in the cell where your this data appears.3 is the place of a text from where excel starts and next 3 is total number of characters you need to extract 541 i.e. 3
Hi Jessica,
If you just need to extract 3 characters beginning with the 3rd one, you can use this formula:
=MID(A1,3,3)
Where A1 is the cell containing the original string.
Can anyone help with a formula for this text string? We need the most recent date from the string. Thanks you in advanced.
Below are a few examples:
CPG PW REV 09-01-2011~No=02-19-2010~Yes=06-01-2005~Business Closing
CPG PW REV 09-29-2010
CPG PW REV 05-11-2012~No=06-08-2012~New Owner~Yes=07-21-2005~Business Closing
How do I extract the $ value?
$0.00 copayment
Rosa:
Enter this as an array formula in an empty cell and then format that cell as Number. Where the data is in C36:
=1*MID(C36,MATCH(TRUE,ISNUMBER(1*MID(C36,ROW($1:$1000),1)),0),COUNT(1*MID(C36,ROW($1:$1000),1)))
Remember, this is an array formula so after you enter it in the formula bar click in the formula and CTRL+Shift+Enter all in one click. Then you'll see the curly brackets around the entire formula indicating Excel knows this is an array formula.
Rosa:
Here's another formula to extract numbers from a string. It's also an array so CTL+Shift+Enter. I think this one works more reliably. So, where the data is in C37
=LOOKUP(10^99,--MID("|"&C37,SMALL(IF(((--ISNUMBER(--MID("|"&C37, ROW($1:$1003),1))=0)*ISNUMBER(--MID("|"&C37,ROW($2:$1004),1))),ROW($2:$1004)),1), ROW($1:$1003)))
Remember to format the cell where this formula is entered.
367 cd 046 104101 Real
131 DPU3005 104017 KRISPY
CC MERCH ID 9189997141
CC MERCH ID 9189997158
POS MID 5930005 - 06/29/2
POS MID 19520212 - 06/29/2
i want to extract 104101, 104017, 9189997141, 9189997158, 5930005, 19520212. what formula can i use to extract this numbers.
Thank you.
Hi please help me in extracting only the numbers here:
a.367 cd 046 104101 104101
b.131 DPU3005 KRISPY 104017 104017
c.CC MERCH ID 9189997141 9189997141
d.CC MERCH ID 9189997158 9189997158
e.POS MID 5930005 - 06/29/2 5930005
f.POS MID 19520212 - 06/29/2 19520212
I tried using this formula =LOOKUP(99^99,--("0"&MID(C7,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C7&"0123456789")),ROW($1:$10))))
it worked for letters c-f but for a-b it only extracted the first number 367 and 131.
Hope you can help me with this. thank you
Jam:
Where the first data set is in A2 this should work:
=TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE(A2,",", " "),LEN(SUBSTITUTE(A2,",", " "))-MAX(IF(MID(SUBSTITUTE(A2,",", " "),ROW($1:$99),1)=" ",ROW($1:$99)))),CHAR(160)," "))
This is an array formula, so when you put it in the formula bar with the cursor somewhere in the formula click CTRL SHIFT ENTER. You should then see the entire formula surrounded by curly brackets.
Copy this down the column to allow this to work on the next data sets.
sir the formula did not work.
367 cd 046 104101 Real
131 DPU3005 104017 KRISPY
CC MERCH ID 9189997141
CC MERCH ID 9189997158
POS MID 5930005 - 06/29/2
POS MID 19520212 - 06/29/2
i want to extract 104101, 104017, 9189997141, 9189997158, 5930005, 19520212. what formula can i use to extract this numbers.
Thank you.
HI,
I have various lines with different patterns and wish to extract only the number. Appreciate your help and advice.
Sa1_TC_Mthly Subs GPRS A/C 515946020_May18
Sa1_FibreBizPac500MStatic_600912789_19Dec17-18Jan18
Sa1_TC_Mthly Subs for A/C 515605183_14Dec17-13Jan18
SA1_TC_Mthly Subs for A/C 601335405_Jan18
Expected results:
515946020
600912789
515605183
601335405
Newbee:
I think there a too many numbers in the string to use a find numbers formula. After working with your sample data the easiest thing I could find was to use the Text-to-Columns tool. Once the data has been split into several cells you can copy the numbers you want and paste them where they're needed. It's nothing fancy, but it gets the job done.
Thank you, Its working.
Can you pls explain,how the function is working?
How can we extract the number from this text.
BOACK#3243004465 Mount
Vikram:
You can use the solution I provided Quinton in the reply directly above your question. It works great for your sample data. Remember after you enter the formula in the cell, put your cursor into the formula in the formula bar and click CTRL+Shift+Enter to tell Excel this is an array.
Henlo, is there any formula to find numbers within a string of text, I'm wanting to make an "=if(" formula for two cells where the sum of the numbers in cell B2 are equal to the single number in B3
Quinton:
This is an array formula so you need to use the keys CTRL+Shift+Enter. In the formula bar you can move your cursor into the formula and then click CTRL+Shift+Enter.
After you do this, there will be curly brackets around the formula which tells Excel it is an array.
=LOOKUP(10^99,--MID("|"&D2,SMALL(IF(((--ISNUMBER(--MID("|"&D2, ROW($1:$1003),1))=0)*ISNUMBER(--MID("|"&D2,ROW($2:$1004),1))),ROW($2:$1004)),1), ROW($1:$1003)))
Enter this in an empty cell and then CTRL+Shift+Enter. You can change D2 to the address you need.
Hi,
I have this type data mainsh98kamal98545 so how to split number and text in seprate columns.
Hi,
I just count numbers mentioned in string,
Ex: String- (123456, 124564, 123456, 321456, 526341, 253614) total numbers count is "6"
Please help for excel.
Yagya:
You can count a specific character like a comma with this formula:
=LEN(TRIM(D85))-LEN(SUBSTITUTE(TRIM(D85),",",""))+1
Where the data is in D85.
In this formula the character to count is the comma in the second spot or the first ",". If your string was separated by - then enter a - in the place of the ,.
In your case this will return 6.
Hello. May be a simple one for you guys but I would like to extract two separate numbers from a text string into separate cells. As in;
Text string: 1 Lines for 50 mins
Result 1: 1
Result 2: 50
Thanks!
Column, name, description
A - text; text with drugs and chemical names (alphanumber). One, two or more names.
B - common_name; common names of drugs and chemicals
C – synonyms; synonyms of drugs and chemicals
D – exctracted_names_origin; names from column A (drugs,chemicals). Between names the symbol ;
E – extracted_names_common
If some row contents two equal names or synonym only, one common_name is extracted in E
A______B____________C____________D___________________E
Text; common_name;synonyms;extracted_names_origin;extracted_names_common
Please copy column by column:
text
DIRECTLY COMPRESSIBLLE ACETAMINOPHEN 73% (E-Z 180)
PARACETAMOL (OR ALTERNATIVELY KNOWN AS ACETAMINOPHEN;CAFFEINE
CAPSULES --ACETAMINOPHEN-325MG; CAFFEINE 40MG
IBUPROFEN SOFTGELS, 200 MG.
SUBSTANCE (+/-)-2-(P-ISOBUTYLPHENYL)PROPIONIC ACID;N-(4-Hydroxyphenyl)acetamide etc
common_name
ACETAMINOPHEN
ACETAMINOPHEN
ACETAMINOPHEN
CAFFEINE
CAFFEINE
IBUPROFEN
IBUPROFEN
ACETYLSALICYLIC ACID
ACETYLSALICYLIC ACID
synonyms
ACETAMINOPHEN
N-(4-Hydroxyphenyl)acetamide
PARACETAMOL
CAFFEINE
1H-Purine-2,6-dione, 3,7-dihydro-1,3,7-trimethyl-
IBUPROFEN
(+/-)-2-(P-ISOBUTYLPHENYL)PROPIONIC ACID
ACETYLSALICYLIC ACID
ASPIRIN
extracted_names_origin
ACETAMINOPHEN
PARACETAMOL;ACETAMINOPHEN;CAFFEINE
ACETAMINOPHEN;CAFFEINE
IBUPROFEN
(+/-)-2-(P-ISOBUTYLPHENYL)PROPIONIC ACID;N-(4-Hydroxyphenyl)acetamide
extracted_names_common
ACETAMINOPHEN
ACETAMINOPHEN;CAFFEINE
ACETAMINOPHEN;CAFFEINE
IBUPROFEN
IBUPROFEN;ACETAMINOPHEN
Thanks!
I have following data.
cell A1 = ap232pple 2017.01.01. aaaaasb
cell A2 = gra2pe s7s 2018.01.01. ssss ed
cell A3 = ora123nge see 2018.03.05 sse
Is there a folmula to extract the date (A1 = 2017.01.01)?
Thank you very much for your help!
I got the formula for this case so far below. I don't know why it does not recognize first digit (2) so I had to manually add it..
=IFERROR("2"&MID(E5,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},E5,1),LEN(E5)+1)),LOOKUP(1,0/MID(E5,ROW(INDIRECT("1:"&LEN(E5))),1),ROW(INDIRECT("1:"&LEN(E5)))) + 1 - MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},E5,1),LEN(E5)+1))),LEN(E5))
I have a column of values (see example below, column A):
What formulas do I use to split the numerical value into column B and the Unit of Measure (alpha characters) into column C, (see example, Columns B & C)??
A B C
1 1g 1 g
2 10g 10 g
3 5g 5 g
4 50mg 50 mg
5 250mg 250 mg
What formulas do I use to split the numerical value into column B and the Unit of Measure into column C, example
Formatting didn't come through correctly...
cell A1 is 1g / want 1 in cell B1 / want g in cell C1
cell A2 is 10g / want 10 in cell B2 / want g in cell C2
cell A3 is 5g / want 5 in cell B3 / want g in cell C3
cell A4 is 50mg / want 50 in cell B4 / want mg in cell C4
cell A5 is 250mg /want 250 in cell B5/want mg in cell C5
AMA:
This requires a different formula for each cell.
In the cell which will contain the number the formula is:
=LOOKUP(99^99,--("0"&MID(H40,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},H40&"0123456789")),ROW($1:$10))))
In my example the cell holding the original data is H40. You can change this cell address to whatever suits you.
The formula which will contain the units is:
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(H40,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""))
Again, my example has the data in H40 you can change that address.
Doug,
Thank you SO VERY Much!...These formulas worked!
I had racked my brain trying so many different approaches and could not find the solution.
Thank you for your help
AMA
Hello, I have quite a difficulty task, can't seem to resolve it.
Example:
Cell A1:
0123456789 Description Item 1 $150.01 0123456789 Description of item 2 $1400.01 0123456789 Description of item 3 $50.01 0123456789 Description of item 4 $15
I want to extract all the prices and sum them.
In cell B2 it should be: $1615.03 - which is sum of 150.01+1400.01+50.01+15.
Note that example in cell A1 can vary with number of items, it can go from 1 to 10 items in total, resulting in 10 prices that need to be SUMed. Also for this text string, what is constant always is the "$" before the price and space (blank) after the price.
I'm able to pull the first price only, but can't quite figure out how to move on to the next price.
Can someone help me out with this?
Djole:
You say you are able to get the first price. How are you able to do this?
Djole:
Because the values were not the same length, the only way I could extract the dollar values was to use Text-to-Columns using the "$" as the delimiter, then where the data had been entered into cell A8 through D8 I entered this: =LEFT(A8,FIND(" ",A8)-1)+0 in an empty cell, then formatted the cells as currency and created a cell to enter SUM(A8:D8).
Is there a way the values can be entered into separate cells as they're entered? Putting all the info in one cell makes it very difficult to work with. Try to catch it at the front end.
Well, at that time i thought i was able with this function:
=MID(A1,FIND("$",A1),8)
But, since the price is variable, it will not work well if the price is $15 for example. I was able though to find the position of each "$" in the cell with the help of FIND and SUBSTITUTE functions.
For the example i mentioned above this will find first "$" position, then it will replace the first "$" and find the second one, etc.
=FIND("$",A2)&"+"&FIND("$",SUBSTITUTE(A2,"$","",1))
Big flaw of this method is that if you have more than 2 prices in the string, it will not work, so you'll need to add a nested SUBSTITUTE in SUBSTITUTE.
To solve this problem i had i got help from yt channel https://www.youtube.com/user/ExcelIsFun. I think they will post the solution in the next week or so.
Could someone please help me with :
"Zadnja: *41,40*
4.6.2018
-0,48%"
I want to extract 41,40 and the cell in wich is located is B2.
Thanks
Could someone please help me with :
"Zadnja: *41,40*
4.6.2018
-0,48%"
I want to extract 41,40 and the cell in wich is located is B2.
Thanks
how to extract only numbers from a text like this "€ 1'234'567" or this "EUR 803'765" ?
ece:
This formula works great. Found it on the web some years ago.
=IF(SUM(LEN(A8)-LEN(SUBSTITUTE(A8,{"0","1","2","3","4","5","6","7","8","9"},"")))>0, SUMPRODUCT(MID(0&A8, LARGE(INDEX(ISNUMBER(--MID(A8, ROW(INDIRECT("1:"&LEN(A8))), 1)) * ROW(INDIRECT("1:"&LEN(A8))), 0), ROW(INDIRECT("1:"&LEN(A8))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A8)))/10),"")
Remember, there are no line breaks in it.
Thanks mate, this is awesome and I got no idea how this is working but it definitely worked...
im looking to extract a series and replace it with "-" to keep the min() and max() values of the series. Then to repeat IF() there are more series sets example:
from this:
100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 506
to this:
100 TO 122 124 TO 142 506
This formula doesn't seem to work when the string of numbers I'm trying to capture starts with zeros, is there a workaround for this?
Indranovich:
Probably the easiest way to do it is to use the Text-to-Columns technique.
Highlight the data you want to split
Under the Data tab select Text-to-Columns
Click the Delimited button, click Next
Select the Space option, click next
In this window you'll see the way Excel will split the text, click Finish
The text will be in separate cells.
Hi all,
can you help me to extract the numbers from this kind of text:
RODC - Jakarta 25105372 **
RODC - Singapore 74297001 **
many thanks
Dear all,
Thank you for the post!
Can someone help me?
I want to extract value of Metre from
AXNASL137/16 JJ80029874 DOBJ JAN17 1.55M @DOLLAR1930.5 = 1.55
AXNASL14/17 JJ 80033100 DOBJ OKT17 10.6M @DOLLAR1830.0 = 10.6
YAH N133/17 JJOB F1 29.05M@DOLLAR1870 MONTREAL = 29.05
AXNASL14/17 JJOB 80033071 DOBJ OKT17 12M @DOLLAR1620.00 = 12
Thank you so much!!!
Zul:
I will provide you with a solution that will work most of the time. It will extract the Metre length from the third string in your sample, but because there is no space before the "@" it will also extract a little more. You'll have to clean this one up manually.
Where the string is in A10 the formula is:
=TRIM(MID(SUBSTITUTE(A10," ",REPT(" ",99)),MAX(1,FIND("M",SUBSTITUTE(A10," ",REPT(" ",99)))-50),99))
First enter this into an empty cell and then copy it down as far as you need. The target cell will update as you copy the formula down.
Thanks Doug....it brilliant!!! This has helped me so much, though i've to make few cleaning. You are a genius. Thank you.
I am trying to extract anything that looks like a credit card number from a string, and this could be anywhere in the cell. Assume a blank always precedes and succeeds the string.
Either xxxxxxxxxxxxxxxx or xxxx-xxxx-xxxx-xxxx.
Can you provide such a solution? I would like to know the string AND I would like to know the position in the string where it begins.
16.91cm 217415 BAKE KING ESSENCE ALMOND
I want to find the number as 217415, can help the formula in Excel
Mary:
Does this number appear in the exact same position in every line of text?
Dear all,
Thank you for the post!
Can someone help me?
I need to separate the numbers from text but in 3 dif collumns.
Examples:
Cataflam 30mg 10 cp| Col1=Cataflam / Col2= 30 / Col3= 10
Diclofenaco potassico Clavulanato 180mg 20cp | Col1=Diclofenaco potassico Clavulanato/ Col2=180 / Col3=20
Thank you very much!
Kind Regards
Aline
Aline:
The only way I could get your result was by utilizing two different multi-step processes. There may be other ways, but this is how I did it.
First, let me say thanks to Ron Coderre a MrExcel MVP who worked through this lengthy discussion back in January 2010 while working with a couple of other folks to arrive at his final formula.
Next, let me say thanks to the folks over at extendoffice where I found this handy piece of VBA code. Here is the address: https://www.extendoffice.com/documents/excel/1625-excel-extract-text-from-alphanumeric-string.html#a1
If you cannot save your workbook as a macro-enabled workbook, you will need to employ a different multi-step process that is not too hard.
To begin here is the data I started with: Diclofenaco potassico Clavulanato 180mg 20cp.
Notice there are spaces between the chemical names and the drug amounts, but not within the drug amounts. Keep your data structure consistently like this and you will be able to use either of these processes. If you do not, the methods will not work.
OK, the multi-step process not using the VBA.
The first part of this process is putting all the data into separate cells.
First, select the data. Second, in the Data tab select Text-to-Columns. Third, in the Text-to-Column window select the delimited button, then next. Fourth, select the spaces button then next. Fifth, select the general button then finish. Now your data is in five separate columns.
The second step is to combine the chemical names into one cell. For the sake of discussion, let us say the data is now in cells A2 to E2. First, select an empty cell where you would like to see the drug name, let us say B3. To accomplish this you will concatenate the names with =Concatenate(A2,” “,B2,” “,C2). Now the drug name is in one cell.
The fourth step requires the use of an array. This means after you enter the complete formula in the formula bar, select the entire formula and select CTRL+SHIFT+ENTER or CSE as the boys in the backroom say. This will let Excel know this is an array and Excel will enclose the formula in curly brackets. Entering curly brackets via the keyboard will not work. So, select the entire formula then CSE.
Keeping the array rule in mind you can extract the numbers. After the text-to-columns step 180mg is in cell D2. So, let us say you would like to see the 180 in C3. Enter this formula in C3:
{=LOOKUP(10^99,--MID("|"&D2,SMALL(IF(((--ISNUMBER(--MID("|"&D2, ROW($1:$1003),1))=0)*ISNUMBER(--MID("|"&D2,ROW($2:$1004),1))),ROW($2:$1004)),1), ROW($1:$1003)))}
After the text-to-columns step 20cp is in cell E2 and keeping the array rule and the CSE part in mind you move to the fifth and final step in this process where you extract 20 to cell D3. To accomplish this enter this formula in D3:
{=LOOKUP(10^99,--MID("|"&E2,SMALL(IF(((--ISNUMBER(--MID("|"&E2, ROW($1:$1003),1))=0)*ISNUMBER(--MID("|"&E2,ROW($2:$1004),1))),ROW($2:$1004)),2), ROW($1:$1003)))}
It is the long way round the barn, but I think you have achieved the result you wanted.
Hi - How do get the number with decimal
Examples : 3.5 days , 4.5 days?, 1 day?, 1 day
I need 3.5,4.5,1,1 pulled out respectively from each text
Thanks in advance
Suresh:
If you have 3.5,4.5,1,1 as text in one cell and you want to separate the numbers into four separate cells select the one cell containing the numbers and then select the data tab then text to columns then choose the delimited option then next then the comma button then finish.
This puts the numbers in separate columns. If you'd rather have the numbers in one column in separate rows then select the numbers in each column, copy them then highlight a cell in one row then from the paste tab choose paste special then the transpose option and the numbers will be pasted into separate cells in one row.
Hi, want to extract the right side of equation.
PARK HILL 3277 = 3277
CONWAY,AR 10667 = 10667
TRADTNL 16849 3a = 16849
ToC 011066 = 011066
Thanks!
Randy:
I think the simplest method is to use Excel's built-in Text to Column tool.
Highlight the data then
Under Data choose the Text to Column tool.
In the Text to Column window choose Delimited then Next
Then select the Other radio button and enter "=" in that field then next
Then if you want to format the numbers as something other than General you can do it here
Then select Finish and you're done.
Hi here is the text "Brd 2Duplex&1VDM Btm 21.5x95.5" , want to extract 21.5 in one cell and 95.5 in another cell , it is not supposed that all description comes with sizes at the end sometimes text come at the end. thanking you.
Can anyone help me to extract and match few alphanumeric consecutive records in an excel
Excel Records - Pattern is like
1) _00004Y74A 4427
2) _00004Y74A 4428
3) _00004Z74A 4429
I have to pick numbers from the right of the string till user get some character /special character.
e.g, 1st record - we have to pick 4427 and ignore _00004Y74A
Then I will go to the next row and find out again numbers from the right of the string till user get some character /special character .
If number is consecutive (4428 in this case) , then I have to match the string before the consecutive numbers e,g,'_00004Y74A' is same for first and second record and next four numbers are consecutive.
As we can see third record doesn’t have same string before the last four consecutive numbers. So this will not be picked
So result will for alphanumeric consecutive invoices will be
1) _00004Y74A4427
2) _00004Y74A4428
This has helped me so much. You are a genius. Thank you.