# Searching For And Extracting Dollar Amount From Text String

May 8, 2014

I have about 2000 rows of text. Each row is a short write up about prospective new business. There is a reference to a projected dollar amount Within 'MOST' of the write-ups. In order to generate a report about the potential dollars being projected, I need to find an easy way to extract the dollar amount from the text.

In most cases, the projected dollar amounts are preceded by "FY2014" then a "\$". However, there are a handful of cases where there is no FY2014, but some variation of the year indicator. Most of the dollar amount entries are written is short text - FY2014 - \$5k, 2014 \$15k. While some others are written out - FY2014 - \$ - \$20,000. In still other cases, within the writeup reference is made to the amount of product projected to be shipped by using the dollar symbols. For example - Estimated ship totals \$\$ for FY2014 = \$5k. I've tried writing some formulas, but as in the last example - the first dollar signs are recognized rather than the dollar sign immediately before the value.

Sample data -
Estimated ship totals \$\$ for FY2014 = \$5K New Customer Prospect 4/9/2012 Customer has still not decided if he

2014 \$15K Funco 4/7/14 working on the program for the demo ...

Over 130 samples tested with about 90% accuracy. FY2014 Ship \$\$ = \$20,000 at least. Setter Line has 7 plants ...

## If The Sum Of A Cell Is A Certain Text Then A Dollar Amount Populates Another Cell

Sep 5, 2009

Attached is an example. Office 2007 & 97 versions attached. If a drop down menu on one sheet selects a certain word, then on another sheet based on that selection a dollar amount is entered. I am guessing this would involve some if commands, I am really weak on making those.

## Dollar Amount

Mar 9, 2009

How can I display the \$xx.xx into a statement?

I have these \$ amounts in column A:
99.12
107.65
14500.99

Have the result in column B as:
(where xx is tenth, hundreds, thousands and yy is the cent)
00000 is to prevent forgery

and to have the results in column C using same column A:
For example like Please Pay Exactly\$000099Dollars and 12cents
or Please Pay Exactly\$000014500Dollars and 99cents

## Dollar Amount Inventory

Sep 3, 2008

I have a total inventoried product dollar amount in a cell. I have 4 other cells with different dollar amounts in each for product I inventory out! In the seperate balance column, as I use a letter ...

## Adding 1 Dollar Amount To All Cells

Jan 6, 2010

I have the new USPS rates in an excel sheet. I need to add my handling fee to all of the new rates. Is there a way to quickly add this same .70 to all cells?

## Concatenate Dollar Amount, Keep Decimals

May 21, 2008

When concatenating a text and dollar amount that is the sum of two cells, adding up to \$42.90, I only get "42.9". is there a function (perhaps similar to ROUND) that will make sure concatenated numbers always have 2 decimal places?

## Searching For String Within Text

Jan 7, 2014

I am using the function Instr to search for a string within some text.

My list contains Apples and Applesauce.

How can I search for only Apples because I seem to be returning Applesuace as well.

Obviously the other way round is not a problem because searching for Applesauce will not return Apples.

## Split One Large Dollar Amount To Multiple Cells?

Apr 11, 2014

I have a group trip coming up where we took in a \$1000 deposit per person. The total amount is only in the main members cell. There will be two more payment amounts coming. I need to make certain each person has paid the total amount they are required for the trip. There are two possibilities for total trip amount. Double occupancy is \$15,500.00 and Triple is \$14,500 per person.

There is a unique Identifier for each registrant, except when there is a multiple person under one registration the identifier has all the same number except at the end. I figured out a way to strip the number down to just show the same number in one registered group. There could be up to 10 people under one registration. I might not have the best option for breaking down the Identifier number, but thought it would be a good start. Used the code Left(column, number).

Issue is this. How can I now take the total number of people under one group take the total deposit and split it between just that unique group so I can then determine what is still owed after payments are made.

## Right Unction But Removing Variable Dollar Amount At Beginning?

Jul 3, 2014

In many cells, a dollar amount (variable lengths) was pulled into the cell. I am trying to use the RIGHT function to remove it but my formula isn't working. A cell example and formula example are below.

P31 = "\$693.00 Research and analyze case law regarding Motion in limine reply"

=RIGHT(P31,LEN(P31)-LEFT(P31,FIND(" ",P31)))

This returns an error, no matter how much I think (wish) it should do what I would like it to.

## Searching For A Number Within A Text String

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.

## Searching For String In Cell Text?

Aug 2, 2012

I have a cell that is populated by clicking a button which pulls info from an external source over which I have no control. I need to confirm the selection made by the user contains one of four quarterly values: Mar, Jun, Sep or Dec. I have this but wanted to know if there is a better alternative or a way to shorten the code.

Code:
If InStr(1, UCase(celltxt), "MAR") Or InStr(1, UCase(celltxt), "JUN") _
Or InStr(1, UCase(celltxt), "SEP") Or InStr(1, UCase(celltxt), "DEC") Then

## Searching For A String In A Text File

Sep 12, 2006

I wrote some code which has compiled a LOT of text files telling me what computers have licence for certain software.

The name of the txt file is the computer name and data within is simple:
--------------------
Found Office
--------------------
as we can see here this computer has 1 licence for OFFICE.
If the string "FOUND" is found then i want to be able to paste the licence name in a spreadsheet and in the next cell underneath paste the filename it was found in the e.g.

VISIO
comp1,comp2,comp3,comp4

WORD
Comp2, comp3, comp4, comp5....and so on......

## Prorate A Total Dollar Amount By A Selected Number Of Attendees

May 29, 2009

I need to prorate a total dollar amount by a selected number of attendees. How can I do that with code, I know the formula for excel, but how do I put that on my user form so it only does the math as a new entry is submitted? I do not want to have the formula on the spreadsheet which is my report unless there is an entry on the row, which will only be the case when a user completes the user form. I just do not want the div/# error on the sheet visible if at all possible.

## Extracting Phone Numbers From Text String Variation In Preceding Text

Mar 29, 2013

I'm pulling phone numbers out of text strings. There is text string ("Office:") indicating that the number following is an office number (the number that I need). MID and FIND take care of this. There are sometimes two instances of office numbers in single text string, so adding a second column using the third argument of FIND lets me start another search past the first instance of an office number. We have a bunch of office numbers with the qualifier "(Text)" after the original "Office" but before the phone number. How do I catch these ones?

## Extracting The Last Name Of A String Of Text

May 15, 2009

I have a column of names, I need to extract the last word

Simon Greaves
Martin John Mackay
Miss E Hoddle
Bob W Blanchflower
Sarah White
D Gascoigne
H G Gomes

Greaves
Mackay
Hoddle
Blanchflower
White
Gascoigne
Gomes

## Extracting Text From String Vba

May 11, 2007

I need to extract the text from a string that is always less the last three digits. For example if A1 is abcdefg I always have to take off "efg" and extract the rest of it to the left but the length of the string can vary. I can be dirty and record a macro where I find the length, subtract 3 and then using the LEFT worksheet function can get my result, but when I come to try to convert that to VBA. What is the VBA equivalent of LEFT worksheet function and how would I go about using LEN in conjuntion with subtracting 3 from it?

## Excel 2003 :: Extracting Text From Text String?

Sep 17, 2013

Date: 17/09/2013 KO: 19:45 Ref: B Malone Att: 7,574 extraction of text from the above text string which is say in A1. What l need is for Date to go in A2, KO in B2, Ref in C2, & Att in D2. I'm using Excel 2003

## Extracting Dates From Text String?

Aug 20, 2014

If I have a cell that contains a sentence and part of a date, for example "delivery due 01/09", I would like a formula that reads that cell, identifies the 01/09 bit and returns 01/09/2014.

The only trouble is that the date would always be variable, so it's more a case of "delivery due #/#"... if that's even possible.

## Extracting Data From A Text String

Sep 8, 2009

I have a few hundred rows of text in the fomat below: 1.23456 xxxxxxxxxxxxxxxxxxxx. The “x’s” represent text which is unique to each row. what the formula I need to extract the number (1.23456) at the start of the string? To complicate things the number may be reported to any number of decimal places, so the formula needs to be able to extract the first block of digits at the start of each row and report it as a number that can be used in calculations.

## Extracting A Date From A Text String

Sep 22, 2009

I have a column of data in the following format:

261D 31/12/2008

There is also a single space after the end of the date.

Can someone please show me a formula to extract the date from each cell in the column? This is slightly complicated by the fact that the number of days (“D”) could consist of 1, 2, 3 or 4 characters, and the date could also be of variable length (e.g. 1/1/2008 vs 31/05/2008)

## Extracting Word From Text String

Sep 11, 2013

I have random text strings which I need to extract the last part from in Excel.

The text string will be made up of code, but the word I need is always to the right of a prefix />

So for instance in A1 I have 76ruihfo:;.>6">/apple

I would want to extract "apple" from the string above into B1.

## Extracting Integers From A Text String

Sep 28, 2005

is it possilble to extract integers from a text string within a cell?

Eg.
Cell A1 - "ABC 123"
I want to extract the "123" from cell A1.

I have many text strings from which I need to extract the integers from.
The trouble is that each text string does not follow a consistent order, so I
cannot use text functions such as LEFT, MID or RIGHT to extract what i need.

## Extracting Values From An Text String (URL)

Oct 14, 2008

I was given a worksheet that contains a column (A) containing about 5,000 URLs (A1 to ~A5000). Each URL string includes three parameters that I want to capture the values.

For example =

[url]

The prameters are known but the values are random up to 256 characters.

I am trying to pull each of the values into a column for sortability and have been mildly successful using MID and FIND but no joy.

## Extracting Text Before And After Numeric String

Jul 31, 2009

I'm trying to extract alphanumeric data before and after a numeric string. The numeric string is in the middle of a URL, which is a varying distance from the start and end.

Here's a sample of my data:

URL Page ID Headline Section quiz.impression-http://www.stuff.co.nz/2677193 2677193

http://www.stuff.co.nz/travel/267779...flying-JetStar 2677794 Why-I-hated-flying-JetStar travel http://www.stuff.co.nz/technology/di...eo-a-smash-hit 1675213 Wedding-dance-video-a-smash-hit technology/digital-living http://www.stuff.co.nz/national/crim...ce-in-Auckland 2678248 Man-shot-by-police-in-Auckland national/crime http://www.stuff.co.nz/sport/rugby/s...it-for-Blackie 3674350 NZRU-set-to-bend-it-for-Blackie sport/rugby/super-14

I start with just column A and generate the other three from that.

I'm using some excellent code from a Mr Excel guru (thanks again Peter) to extract the 8-digit numeric string in column B, I just need C (after) and D (before).

That macro is:
Dim Bits
Dim c As Range
Dim i As Long

For Each c In Range("A4", Range("A" & Rows.Count).End(xlUp))....................

## Extracting Both Numbers And Text From A String

Jun 21, 2007

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:Extract Numerical Data

Function midNumber(inputStr As String) As Double
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

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?

## Extracting Data Between 2 Characters In A Text String

Dec 14, 2013

I have the data string below:

Career:25: 1-0-2 \$13,765

I would like to extract the 1 between the : and - and as a seperate extraction would like te 2 between the - and the \$ I have tried a few things but end up with the - as the length of the data changes

## Extracting Phone Number Out Of String Of Text?

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

## Extracting Number From Inconsistent Text String

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.

## Extracting Text String Values From A Cell?

Jul 8, 2014

I am trying to transfer data from some appointment based software into a spreadsheet .
the data comes out as one row For example ,the following is the contents of cell A1: 06/06/2014 09:00 AM - 09:30 AM Patient: John Smith

What I would like to do is extract one column with the date ,one column with the patient forename and one with the patient surname.

I have tried various combinations of =RIGHT(A1,LEN(A1)-FIND(".",A1)) etc etc but cannot extract the data I need

## Extracting Text From A String With Variable Length

Jan 13, 2009

Hey I got a long String like this "[...] increase of x.xx% [...]".

I am trying to extract only the percentage number which can be of variable length, so maybe 900.99% or 9.99%.

I tried this formula:
=MID(G14,SEARCH("%",G14)-5,5)
but this one doesnt bring the right results as the percentage figure is often not exactly 5 characters long.