Finding A Number Within A Text String
Oct 28, 2007
I want to find a number within a text string, then count the letters in the same string, then add the number to the number of letters.
For example, if I have 9RR in a cell, I want to add 9 + 2 = 11.
The data will always be arranged with the number on the left side of the string.
So, if the number is a single digit, I know I can use left(A1) + len(A1) -1 to get my desired result. But, the trouble I am having is what if there is a double digit number like 10RR...I'm not quite sure how to create a formula (without using VBA) to give me a result of 12 for this cell.
View 9 Replies
ADVERTISEMENT
Jul 31, 2014
I'm trying to extract the number portion from the following string. I'm trying to use FIND to find the first digit between 1 and 2 so I used an "or" argument along with the code number, however it's not working here. What I really want to do is find any digit between 1 and 9 in the string, however I started with this formula for now. why this doesn't work and provide a better formula. I want to understand why this didn't work to understand the functions better. Text string is as follows "standard VAT rate: 20% (Jan 1984)". The formula I used in the following =MID(A253,FIND(OR(Char(49),CHAR(50)),A253),2) where A253 contains the string. Is it possible to use a logical argument within a find?
View 7 Replies
View Related
Apr 8, 2007
We are trying to make either a macro or a function that will look through the contents of a cell, find the first NUMBER and then paste the results to another column. Below is an example of what may be in cell A1:
#BC7K,03/30/2007,0.00636,0.0069,0.00614,0.0062,0.0,0
We want only to find the FIRST NUMBER in this string, so the result should be 7.
View 5 Replies
View Related
Jun 29, 2014
I need to create a macro to do the following:
Search the activecell for a text string (a), and then either paste in text string (b) at the end of the cell if (a) is found, or text string (c) if (a) is not found.
For example, if the activecell has "AA/" in it, I want the cell to become "AA/01" (pasting in "01" at the end), and if the cell has just "AA" in it, I want it to still become "AA/01" (pasting "/01" at the end). The macro will be linked to a commandbutton.
View 7 Replies
View Related
Oct 24, 2008
On my spreadsheet I have a column entitled: "Item Title", and the data within this column can look like the following;
View 3 Replies
View Related
May 11, 2010
In a data report i have a column which contains a mix of text and may contain an 8 digit number which could start in 0.
for example
in column A i could have "Hello how are you 01237232 I am fine"
I wish to extract the number into a seperate column, and would ordinarilly use a mid or left/ right function, however the text infront and behind the number will vary in length, which means i cannot do this.
The number will always be 8 digits, could start in 0 but will not always, and it may not be present in all the cells in this column.
How I an achieve this?
View 11 Replies
View Related
Apr 10, 2008
I have a mass of text in a cell. I would ideally like to search the text to format in a different font colour so it can be easily seen where the specific text is.
My current way is to use SEARCH in conditional formatting but this changes the whole cell not the specific text I am interested in.
EG. - I interested in the text PETER in my cell so would like PETER to be in red font.
A peck of pickled peppers Peter Piper picked; If Peter Piper picked a peck of pickled peppers, Where's the peck of pickled peppers Peter Piper picked?
View 10 Replies
View Related
May 21, 2008
i need help with 2 formulas to return text within a longer string.
the first result i need is a style #. this will always start at the 5th character, but the length is variable. it will always end right before the last "/" or "" in the string (there may or may not be a / or within the style number)
the 2nd result i need is a description of the style. this will always be after the last "/" or "" in the string and end before a ".", but there may not always be a period in the string.
Text string: ABC123123BASIC TEE.black
Style # result: 123123
Description result: BASIC TEE
Text string: DEF/4567LEAH/FLORAL TANK DRESS
Style # result: 4567LEAH
Description result: FLORAL TANK DRESS
View 10 Replies
View Related
May 12, 2014
I have a report that in column BX has a large string of text (html information). Within this text there is a phrase called "| Hear = Education Website |" The phrase Education Website can be a variety of things though (Up to 9 choices). What is a formula I could use to just pull out the text after "Hear =" but before |""?
View 8 Replies
View Related
Jun 16, 2012
Title should read: Finding the nth Occurrence of a character within a text string
I have a very long text string that is delimited by about 50 "/" to segment certain values within the text string. I want to be able to extract the text between the 33rd and 34th occurrence of "/". How to do this?
View 5 Replies
View Related
Mar 12, 2008
Is there a function in VBA that is similar to either the FIND function or SEARCH function in Excel? The arguments for the FIND function in Excel are FIND(find_text, within_text, [position]).
I have a text string in VBA ("$A1:$D$13") that I want to be able to identify the first "$" and then later the ":". I'm getting tripped up on the 3rd line of code. Thanks a million.
Sub page_set_print_area()
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$13"
x = ActiveSheet.PageSetup.PrintArea
Position$ = Search("$", x, 0)
End Sub
View 9 Replies
View Related
Oct 3, 2011
I'm trying to restructure a list of files at work into a format that makes some sort of sense and can be filtered into useful information.
Part of what I need to do, is match up all the drawing numbers that a certain project references. The numbers are formatted like "####A##" ex. 1234A01,1234A02,5678A01, etc. The cells that I need to extract drawing numbers from contain some description or other text (not just the drawing number) so I need to extract the drawing number from that cells value. (Ex. I need to get the drawing number 1234A01 from a cell whose value is "blah blah 1234A01 blah blah blah")
I was thinking I could just search the string for "####A##" but I'm not really sure how....I tried instr, but I think its searching for the literal value of "####A##" rather than treating the # characters as wildcards...
I tried:
Code:
if rngNames.Cells(intx, 2).Value Like ("*####A##*") then
set intStart = instr(1,rngnames.Cells(intx,2).value,"####A##")
'....
end if
but intStart remains 0, so this method is not working...
View 3 Replies
View Related
Aug 7, 2009
Possible to find the position of the 1st number value inbetween text??
example : GBB_5600_OPP_WNN
View 9 Replies
View Related
Apr 28, 2014
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?
View 3 Replies
View Related
Sep 4, 2009
I have a list of English game results with the score and the soccers of the game.
E.g. : [Steinsson 34, Davies 41, Elmander 46; Fuller 91]
As you can see in the excel, what Im trying to do is to find the time i.e which is the numbers in the field and display them seperately onto the cells beside. So what i need is to Find and extract these numbers 34,41,46,91 and paste them just beside cell on the list.
I can use formula but i can only display 1 number, so i think this might require a VBA script.Thanks in advance guys..
View 10 Replies
View Related
Mar 6, 2008
I have in a cell a text string and I want to add a number automatically in the following rows. With other words
Product01
Product02
Product03
Etc
Product is a constant
How will I do this I know I can type the numbers and CONCATENATE it with the text part but I am too lazy to type out all the numbers to 1000
View 10 Replies
View Related
Jun 2, 2009
I have a list of customers and account numbers contained within a cell. I need a formula if possible that searches from the right of the cell and then returns the all the numbers. e.g.
Arsenal1234Required formula result 1234
Liverpool2456Required formula result2456
Chelsea100564Required formula result100564
I can not use text to columns as they data is not consistant. Also worth noting is that the customer numbers vary fro 1 digit to 9 digits long.
View 5 Replies
View Related
Jul 16, 2012
I'm trying to pull a specific 10 digit number from a text string. The ID # is always 10 digits however sometimes there are dates in the data that need to be excluded as well as other numbers such as "I-9". The 10 digit number will always start with 1 or 8. Is there a macro or Formula to extract this data? Please find the example dataset below.
Edit: I should specify I want the "100118416" number from the first cell example or the "800011320" number from the 10th example.
NDM Abxx, Kexxxxx 100118416 2-29-12.pdf
VPG I9 Aberxxxxx, Dexxx 100113048 1-1-12.pdf
I-9 Abxxx, Rexx 100119966 4-5-12.pdf
NDM I9 Achxxxn, Paxx 100119008 3-15-2012.pdf
VPG I-9 Acxxxxx, Toxx 100112782 01-01-12.pdf
[code].....
View 9 Replies
View Related
Aug 10, 2013
I need to take 12ABC1234512345 and turn it into 12A BC 12345 12345 with a vba macro. I've made a formula that works but in this situation a macro would be more ideal.
View 5 Replies
View Related
Jul 24, 2007
I have been trying a few formulas to return numbers from a string of text. The problem I am having is that there are more than one set of numbers that I need returned.
Here is an example of the text entered in F2
"K2PT bus structure [2 locations] & strain structure South West of L9L12-12 [1 location] / Use #4/0, std, cu conductor, connector Cat ID#10842 at structure leg & connector Cat ID#9655 at #4/0, std, cu gnd loop to connect #4/0, std, cu gnd loop to str leg."
What I need returned in a cell H2 is the numbers beside the Cat ID#
10842, 9655
I tried a Return Right Value formula, but I think because there are more than one possible value it isn't working properly.
View 5 Replies
View Related
Jan 11, 2008
I receive a text file daily of between 100 to 50,000 rows. It is a combination of many smaller text files or " records". Each record contains a row containing the name and some particulars . From 1 to 5 rows below that row there may or may not be a row containing the score for that record.
Here is an example of what the rows look like;
4505329 64036593 150090 MS MARY SMITH AB Finished
CRP 3.0 SCORE: 400
From the first row I need to pull out the second string of digits (ex 64036593) and the person's name. From the second row I need to extract the score (400). I would like to copy the three pieces of information to 3 separate columns on another sheet.
Comments
1. the word "Finished" always appears at the end of the row with the name in it but it also appears at the end of every record in it's own row like this;
Finished No Note
2. the strings of digits in the first row can vary in length but there are always 3 of them and they are always seperated by spaces.
I need to extract all the names and scores and put them in a table. If a name is not followed by a score I need to put "no score".
View 9 Replies
View Related
Oct 24, 2008
I am looking for a way to add the 123 plus 456 to get 579. I have had some ideas that almost work, but I cannot get rid of the ' .
View 11 Replies
View Related
Oct 3, 2010
We often get spreadsheets from our customer that are formatted with both comma and dash combinations. We would like to only have the comma seperation.
Example:
R1, R2, R3-R5, R30
Result:
R1, R2, R3, R4, R5, R30
There is usually only one alpha character but sometimes more. Example: CR1, CR2, CR3-CR5.
The following macro works great if there are no alpha characters. how to solve the alpha/numeric combination?
Function Nums(rng As Range) As String
Dim adnum As Integer, n As Integer, num, Txt As String
num = Split(rng, ",")
[Code]....
View 9 Replies
View Related
Apr 30, 2013
Is there a way to extract a phone number out of a string of text. The phone number will always be expressed the same way (eight digits seperated by a space half way through) - 9999 9999. But the text before and after the number is random. Is there a macro to do this on a large scale? The method needs to be applied to hundreds of these strings, each on a new line.
An example of the string of text with the phone number underlined:
Alesdro 0 63PnantCey4281 5919 Aledio daSisaon' Brglry
View 7 Replies
View Related
Jul 4, 2014
I have an excel stock price template, where I need the current price to calculate the mid point if the chart.
This is the text I get from the webquery
Underlying stock: SBIN 2699.00 as on Jul 04, 2014 15:30:36 IST
I need a formula to extract the stock price '2699.00' only from this text.
The problems are sometimes its may change to underlying "stock" to "index", SBIN to RELIANCE or TATASTEEL, and the price some times two digit say 16, some times 6 letters say 150.05, or 8 digits say 15160.00
Formula, the text in B1 and need the price at A100.
View 9 Replies
View Related
Mar 24, 2009
I would like one formula to remove "5206" from this string. I tried a combination of right/left formulas together, but I could not get it to work.
5032.5206.05816.00040501.0000
View 9 Replies
View Related
May 5, 2009
I am trying to reference a Name of a place from an order number. To illustrate, University Park, IL can have an order # of 6598641373. The only thing is, all I need to reference is the first four digits, 6598. The other worksheet does not have city and state names, they only have the order #s.
View 10 Replies
View Related
Aug 24, 2006
If 'a' is a string that contains x number of characters, how do I find out what x is (in VBA)?
View 2 Replies
View Related
Feb 21, 2014
Formula to match all text and closest number in alphanumeric string
View 1 Replies
View Related
May 25, 2007
I have a very large spreadsheet and want to count the number of times a particular text string shows up in a column. I can't use autofilter due to the 1000 limit.
Here's an example, Column C contains:
Dan Parker
John Doe
Dan Smith
Jill Smith
So if I search on *Dan*, the function should return a count of 2.
I've used COUNTIF before to return values when the whole cell = a certain value but in my case the cell may have 200 characters and I want to count based on a fuzzy search. I would like to do this in a function and not a macro.
View 14 Replies
View Related
