Free Microsoft Excel 2013 Quick Reference

Extract number from text and cell Results

Example1.xlsxHi All,

I need help with a formula.

What I am aiming to do is to search a cell, look for a particular string of text and then return the numerical value that most closely preceeds that string of text. I cannot simply search for a number as there may be more than one numerical value in the string.

Example (sheet attached):

Cell A1:

3250 Series Hydraulic Pump, 350 bar test pressure

So from the above I would like to search the sting of text to return "350" in cell B1. It would have to ignore the first numerical value, so I was looking for a way to search for "bar" or "test" and then return the closest preceding number into cell B1.

Does anyone know of a forumla that would extract the number from the string in this manner?

Thanks.

Afternoon all,

I have been using this code to extract data from txt files


	VB:
	
 
Sub DelRows() 
     'File 1
     'Import Comma delimited txt
    Workbooks.OpenText Filename:= _ 
    "V:HQNDSGroupsFleet DataRoad FleetSuppliersAllStar Business Solutions Limited (Fuel Accounts)PaymentsFuelImport1.txt" _ 
    , Origin:=xlMSDOS, _ 
    StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ 
    ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _ 
    , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _ 
    Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _ 
    Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _ 
    16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), _ 
    Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array( _ 
    29, 1), Array(30, 1), Array(31, 1)), TrailingMinusNumbers:=True 
     
     'Sort AZ
     
    Cells.Select 
    Range("A1").Activate 
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ 
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
     'Delete Rows based on Column A being an 8 digit number
    Lstrow = Range("A65536").End(xlUp).Row 
     
    For x = Lstrow To 2 Step -1 
         
        If Len(Cells(x, 2)) > 8 Or Len(Cells(x, 1)) > 8 Then 
            Rows(x).EntireRow.Delete 
             
        End If 
    Next x 
     
     
     
     'Insert new column A copy and paste AE into A and Format E to number
     
    Columns("A:A").Select 
    Selection.Insert Shift:=xlToRight 
    Columns("AE:AE").Select 
    Selection.Cut 
    Columns("A:A").Select 
    ActiveSheet.Paste 
    Columns("E:E").Select 
    Selection.NumberFormat = "0" 
    Rows("1:1").Select 
    Selection.Insert Shift:=xlDown 
     
     'Copy headers in from new sheet
     
    ChDir _ 
    "V:HQNDSGroupsFleet DataRoad FleetSuppliersAllStar Business Solutions Limited (Fuel Accounts)Payments" 
    Workbooks.Open Filename:= _ 
    "V:HQNDSGroupsFleet DataRoad FleetSuppliersAllStar Business Solutions Limited (Fuel Accounts)PaymentsArval Finance column
headings.xls" _ 
    , Origin:=xlWindows 
    Rows("1:1").Select 
    Selection.Copy 
    Windows("FuelImport1.txt").Activate 
    ActiveSheet.Paste 
     
     'Resize column and row
     
    Cells.EntireColumn.AutoFit 
    Cells.EntireRow.AutoFit 
     
     'Name sheet Tab
     
    ActiveSheet.Name = [B2] & "_" & Format([F2], "yyyyddmm") 
     
     'File 2
     'Import Comma delimited txt
    Workbooks.OpenText Filename:= _ 
    "V:HQNDSGroupsFleet DataRoad FleetSuppliersAllStar Business Solutions Limited (Fuel Accounts)PaymentsFuelImport2.txt" _ 
    , Origin:=xlMSDOS, _ 
    StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ 
    ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _ 
    , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _ 
    Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _ 
    Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _ 
    16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), _ 
    Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array( _ 
    29, 1), Array(30, 1), Array(31, 1)), TrailingMinusNumbers:=True 
     
     'Sort AZ
     
    Cells.Select 
    Range("A1").Activate 
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ 
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
     'Delete Rows based on Column A being an 8 digit number
    Lstrow = Range("A65536").End(xlUp).Row 
     
    For x = Lstrow To 2 Step -1 
         
        If Len(Cells(x, 2)) > 8 Or Len(Cells(x, 1)) > 8 Then 
            Rows(x).EntireRow.Delete 
             
        End If 
    Next x 
     
     
     'Insert new column A copy and paste AE into A and Format E to number
     
    Columns("A:A").Select 
    Selection.Insert Shift:=xlToRight 
    Columns("AE:AE").Select 
    Selection.Cut 
    Columns("A:A").Select 
    ActiveSheet.Paste 
    Columns("E:E").Select 
    Selection.NumberFormat = "0" 
    Rows("1:1").Select 
    Selection.Insert Shift:=xlDown 
     
     'Copy headers in from new sheet
     
    Windows("Arval Finance column headings.xls").Activate 
    Rows("1:1").Select 
    Selection.Copy 
    Windows("FuelImport5.txt").Activate 
    ActiveSheet.Paste 
     
     'Resize column and row
     
    Cells.EntireColumn.AutoFit 
    Cells.EntireRow.AutoFit 
     
     'Name sheet Tab
     
    ActiveSheet.Name = [B2] & "_" & Format([F2], "yyyyddmm") 
     
    Workbooks("Arval Finance column headings.xls").Close SaveChanges:=False 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The problem is that the dates going into column F are being formatted from GENERAL to DATE format and when this happens it turns the cell from DDMMYYYY to MMDDYYYY. This wouldn't be a problem if it did it for the entire column but it only does it for random cells.

Any ideas why this is happening?

Stu

Hi,
I have a cell in my worksheet with a fully qualified filename like 'D:abcDefGHIJklxyz123.app

I would like to extract only the xyz123.app. Obviously, the number of characters is going to vary based on the filename. The find and search functions appear to help locate the cell but not the substring in the text. The right and left appear to work based on number of characters and in my case these are varying. Also, there does not appear to be any function that can do a search a string from right to left.

Can you please help with this?

Thanks

Sorry, i know there are quite a few post for extracting text etc but non seem to fit and I'm still very new to macros

Im trying to extract varying alphernumeric values from a column containing non standard free type cell.

the alphanumerics can be represented as follows
N=number
X=Letter
S=Space

NNXXXNNNNNN
NNXSXXNNNNNN
NNXSXXSNNNNNN
NNXSXXSNNNSNNN
NNXXXNNNSNNN
NNXXXSNNNNNN

Im hoping to copy the alphanumeric into the cell adjacent right

any help would be greatly appreciated

im trying to learn but the more difficult stuff is still stumping me

thnks in advance hope someone can help

bspoony
"for we are all as magpies... till the ravens comes to call"

Hi all,
I have a listbox populated with a number followed by description. How do I write code to extract the number and description to different cells.
Listbox example: "1234ES - Project Description"
Now I want "1234ES" in one cell and "Project Description" in cell left of number.
Any help would be much appreciated. Tahnk you

Hi Guys

There are meny posts regarding how to extract the numbers from alpahnumeric text but none for the opposite; which is extract text from an alphanumeric cell. Or maybe I am not doing my searchs that well.

http://www.ozgrid.com/VBA/ExtractNum.htm
http://www.ozgrid.com/forum/showthread.php?t=73053
Extract Numbers And Text From Listbox Separated By Delimiter

Can someone please help me to extract the text part from cells that have values like,

Goodwood -2
Tabel Bay -2
Chekered -3

I only want the Goodwood, Tabel Bay part of the cell.

Regards

I am using the Left & Right functions to extract and add numbers from 6 character mixed text/numbers (see attached workbook) where the format will always be the first 3 chars as text and the second 3 as numbers. This works fine for the 8 rows in the attached example, but I need to be able to apply this to 100 rows, and the formula will be just too long & unworkable.

The element of the formula to look at each row is =IF(LEFT(C3,3)=B13,+RIGHT(C3,3))
so for two rows it would be =IF(LEFT(C3,3)=B13,+RIGHT(C3,3))+IF(LEFT(C4,3)=B13,+RIGHT(C4,3))

I have no idea how to apply this to 100 rows, short of doing 25 at a time in 4 different cells and then adding these together (but this isn't very practical).

Any help would be greatly appreciated!

Thanks

DJ

Hi

I have text as numbers downloaded to excel sheets from crystal that i need to add together on a summary sheet. I've tried using Value, TRIM and CLEAN functions to no avail. the TYPE function is reading the cell as Text and I have also tried multiplying by 1. Does anybody know how I can get around this ? hopefully I've attached an extract of the file

Andrew

Hi,
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.

Anyone remember how to do this please?

Thanks

Dave

Hi all,

I have a woorkbook where I have a cell that has the following apperance:

12345678-ABCDEFG

The numbers can vary in length and as well as the text.

What I am trying to do is to split this cell into to columns.

Column 1 shows the account number, Column 2 should show the Text and the "-" sign should not be in any of the columns ie it should look something like this

Column1 Column2
12345678 ABCDEFG

I have found a useful function for the first part thanx to Mikerickson
URL:http://www.ozgrid.com/forum/showthread.php?t=68969

	VB:
	
 
    Dim i As Long 
    For i = 1 To Len(inputStr) 
        midNumber = CDbl(Val(Mid(inputStr, i))) 
        If midNumber  0 Then Exit Function 
    Next i 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But how do i seperate the text and how do Crete a macro that would loop through some 2000 rows and create a column A and B from Column A?

Thankful for any help.

Robert

Hi,

Your forum was recomended to me by a friend whom you guys have helped in the past so here goes.

I am importing into Excel a list of contacts from a txt file so I can make a mailing label database. I am able to seperate out the various parts of the address just fine but here's where it gets tricky.

Below is a example of the text I am importing:
Mr Dow Jones, 600 DIXON ROAD, TORONTO, ON M9W 1J1, CANADA (1-416-6757611)

I need Excel to look at the cell containing the country name and: 1st. see if there is a phone number present (this is the numbers between the () and if there is - place the phone number in Cell F1 and place the Country in Cell E1 but strip out both the () and the phone number. If there is no phone number present (and therefore no () I need Excel to go ahead and place the country name in E1

I have tried writing my own formula's to do this but I think its just a bit too hard for me to work out so if any can make a suggestion then that would be great.

Thanks

Hi!

I have a worksheet with only 1 column populated bu 2 types of values:
text and numbers.

I try to figure out a macro to extract each number after a text value till the next text value occurs into the cells in the row of the first text value.
Amount of numbers after text is variable.

Would be grateful if anyone could help. I attached a file to picture a problem.

My regards,
Luker

Is there a code that will search through a workbook's worksheets extracting numbers or text from cells A1 and B1 and if the cell B1=0 skip. Then put all the data in columns A & B on a feed sheet, I would like this to update automatically when info in cell B1 on any worksheet updates. Would save me time daily!!

I want to automate the creation of $$$ files for my work but don't know
honestly how to do this. I usually follow the procedure below to do
this manually (Is there a way to do this programmatically?)

1. Looks at the value of Row 10 column O if the date indicated is the
current date
2. If it's the current date creates a dollar file (plain text with $$$
extension) based on the sample.$$$ template (some are constant; some
values are variable depending on the values indicated in the sample
master tracking.xls)
If any of the values in column O does not correspond to current date,
does nothing (exits the subroutine)
3. Extracts values from columns indicated in the Sample.$$$ template
4. Saves the $$$ file based on the value indicated in Column U of
Sample Master Tracking Log.xls with ".$$$" extension
5. Repeats Step 1-4 until it encounters a blank cell in Column O.
6. The following values are extracted from cell values in Sample Master
Tracking Log.xls to Sample.$$$

Date Shipped: Value taken from Column B of Sample Master Tracking
Log.xls
Date Received: Value taken from Column C of Sample Master Tracking
Log.xls
Date Due: Value taken from Column M of Sample Master Tracking Log.xls
Date Transmitted: Value taken from Column O of Sample Master Tracking
Log.xls
Transmission Filename: Value taken from Column U of Sample Master
Tracking Log.xls
Julian Shipment Number: Value taken from Column D of Sample Master
Tracking Log.xls
Customer Shipment Number: Value taken from Column E of Sample Master
Tracking Log.xls
Billing Item Number: Value taken from Column H of Sample Master
Tracking Log.xls
Total Number of Billable Records: Value taken from Column L of Sample
Master Tracking Log.xls
Batch: Value taken from Column I of Sample Master Tracking Log.xls
Type: Value taken from Column L of Sample Master Tracking Log.xls
Other information: Value taken from Column P of Sample Master Tracking
Log.xls

7. Source data are located at C:My Reports

Attached is a zip file (Sample Files.zip) containing the following:

Sample.$$$ - template dollar file
Sample Master Tracking Log.xls - template master tracking sheet

I would appreciate any of your help and ideas on this.

Thanks

Johnny

Hi, I have an excel file with the following data column

10mm
14.2x198.3x23mm
~3-5.5mm
10 x 15.5 x 20mm

I want to extract the max number of each cell and write to the cell next to it, so the result would be

10mm 10 14.2x198.3x23mm 198.3 ~3-5.5mm 5.5 10 x 15.5 x 20mm 20

Can anyone help me? Thanks a lot.

Hello forum members,

as this is my 1st post first of all I want to say hello to everyone here .

The problem I have is, as the topic title already says is to extract cell data depending if they have a specific text or symbol and return numers basicly to some other cell.

I basicly need to do something for college with it, its about doing a sort of analysis depending on the area of some polygons. I need to prepare this file where I have the numbers of some parcels, area, and other info.

I will describe what I'm looking for, but if you want to help me you will have to download a part of the file I'm working on. So if you know how to help me, please do it by downloading the file first ^^.

If you open the file you will see that i colored some rows, these are the rows which contain right of the original data a exmaple how it should look like.

In the column "NUMBER"(which is the number of some parcel) i need to get rid of the "Z" character in every one of them. I would ussually solve that by just replacing "Z" with nothing basicly to get that done, but because some parcels, which have been divided get a sub-number excel automatically divides that number into a date.

Aditionally, about that sub-number. I need to get every sub-number from the original number extracted to the column right of it called "SUB-NUMBER" obviously.

For example i got Z19/4.
I need to get rid of "Z" while preserving "19" in the NUMBER column and having "4" in the SUB-NUMBER column.

I searching around the net and found out that some sort of this can be done via the RIGHT,MID,LEFT commands(that doesnt mean that it has to be done this way), but I haven't had much success with it.

Please note that I also got some parcels with numbers like for example 10215/4 with or without the "Z" char, so the formula shouldn't be dependant on the number of chars the cell contains, or on the fact that it contains a "Z" char or not.

Help would be appreciated :D

Hi I have the following code, the objective is to extract firstname and surname from a worksheet named "View all Data" and to add the full name (first name and surname )to a list box if the person has missed 50% of tests. At present the following sub works by adding the first person in the worksheets first name and surname separately to a list box if the percentage missed is 50%. However, if the first person i.e. nat vance hasnt missed 50% of tests, but the person lyndsay has, then the name nat and vance are still added to the list box as separate names.

'Declarations
Public Const StartCSVvalues = "C6"
Public Const StartSurname = "D6"
Public Const StartCSVdata = "C5"

Code:
Sub missedFiftyPerCent(LB As MSForms.ListBox)
'Declarations
Dim C As Range, lngLastNameRow As Long, lngStartNameRow As Long, lngNameCol As Long
Dim lngFirstCol As Long, lngLastCol As Long, lngFirstRow As Long, lngLastRow As Long, lngMissedTests As Long, lngTotalTests As Long
Dim lngRow As Long, strEval As String, sngPercentageMissed As Single
Dim lngSurnameCol As Long, lngSurnameRow As Long
lngNameCol = Range(StartCSVvalues).Column
lngSurnameCol = Range(StartSurname).Column
lngStartNameRow = Range(StartCSVvalues).Row
lngSurnameRow = Range(StartSurname).Row
'set row to be the last cell
lngLastNameRow = Range(StartCSVvalues).End(xlDown).Row
With CSVsheet

'define the first column
lngFirstCol = .Range(StartCSVdata).Column + FirstColumnWithTests - 1
'Find the last column
lngLastCol = .Range(StartCSVdata).End(xlToRight).Column
'Find the first row
lngFirstRow = .Range(StartCSVdata).Row + 1 '+1: first row contains headers
'Find the last row
lngLastRow = .Cells(65536, .Range(StartCSVdata).Column).End(xlUp).Row
'Find the total number of tests
lngTotalTests = lngLastCol - 5
'Count first row to the last
For lngRow = lngFirstRow To lngLastRow
strEval = "countif(" & .Cells(lngRow, lngFirstCol).Address & ":" & Cells(lngRow, lngLastCol).Address & ",""AB"")"
'strEval = "countif(" & .Cells(lngRow, lngFirstCol).Address & ":" & Cells(lngRow, lngLastCol).Address & ")"
'Debug.Print strEval
lngMissedTests = Evaluate(strEval)
sngPercentageMissed = 100 * lngMissedTests / lngTotalTests
'MsgBox sngPercentageMissed
' For lngStartNameRow = lngStartNameRow To lngLastNameRow
If sngPercentageMissed = 50 Then
LB.AddItem .Cells(lngStartNameRow, lngNameCol).Text
LB.AddItem .Cells(lngSurnameRow, lngSurnameCol).Text
End If
' LB.AddItem .Cells(lngStartNameRow, lngLastNameRow).Value
'Next
Next

End With
Set LB = Nothing
End Sub

Does anyone know how to append the two names together? And how to display the name of the person who actually missed 50% of tests and not simply the first person whose name is the row?

Many thanks for any help or suggestions !

Natalie
puzzled:

Cell in excel containing large amount of text. Contains the following text one or more times (including the speech marks):

"Provide Dwd/Dwl Number: DWD*****"

What I need to do is extract all of the DWD values from this cell, load into an array and dump them into another cell. It's the extraction bit that I need to sort out.

In my mind the way that the code should work is:

Find all instances of 'dwd'
For each instance of 'dwd'
check the next/proceding character.

if the character is a space, check the next one
if the character is not a number or space, break and go to the next instance of 'dwd'
if the character is a number add it and the next 5 characters to a temp variable (varTemp)
loop

take the value of varTemp, prepend it with 'dwd' and add it to the running list/array

Loop

Output the full list of DWDs
Any ideas?

Hi all,

Basically I want to change the character ~ with . in order to be able to make them numeric values to be feeded to other functions. But REPLACE seems not doing the job so I've been checking out other options such as seperating after and before the character ~. Details are below.

I've been trying to use this formula to extract values from a delimited database which I open with excel.

The formula that has brought me close is =IF(ISNUMBER(E51)=FALSE,LEFT(E51,LEN(E51)- FIND("~",E51)),E51)

14010~000
3210~0000

When I import the database, the figures above have originals as 14010.00000 & 3210.00000 but transfer to excel as above. As far as I have observed 9 character spaces are displayed & the DOT transfers to ~ for some reason. I need the LEFT section of the ~

Can someone help me. The code above works when there are 4 zeros on the right but fails when there are more than 4 numbers on the RIGHT of the ~

Cheers

Greetings everyone. First time using the site and it looks really neat.

I have a repetitive task that I am attempting to make a macro for. It requires me to scan a column of cells worth of data (characters) for parts of what they contain and place those parts into a variable or string so that I can use an IF statement to place text in another cell.

I used to know someone who could do this via VBA, but I have been unable to reach him.

This sheet is fairly basic. The cells to be scanned are all in one column, they are all four digit numbers and I need to read the first two digits into separate variables.

Example: If the cell has "4101" in it, I want to be able to read the first digit "4" into one variable and the second digit "1" into another variable.

This way I can place text based on either one of those digits into the next cell over with an IF statement and can enclose it into a large loop to do the entire column.

If anyone could walk me through the code to read the cell and extract characters from it so I can do this in the future for different types of cell values, that would be really appreciated.