Sumproduct With Text String

Aug 4, 2008

I have trying to write a sumproduct formula, however, the total sum amount that i get is "0", which i think the formula is not picking up the text.

=SUMPRODUCT(($Y$2:$Y$65536="Evaporative cooler*")*($Q$2:$Q$65536=$AM$5)*($N$2:$N$65536=6),($AA$2:$AA$65536))

Y column is the text column to find the Evaporative cooler, the Q is another column where i have write the text out at AM5, N is a number field. The only bracket that is not working is the Y. Can you please let me know if I did anything wrong to it?

There are 3 types of Evaporative cooler, 1) Evaporative cooler - phoenix, 2) Evaporative cooler - Cal, 3) Evaporative cooler - Brisa. I wondered if I can use "*" to select all of the Evaporative cooler, instead of listing all three.

ADVERTISEMENT

Sumproduct With Text String (wildcard)

Aug 19, 2008

I am having problem filtering out criteria by putting together a sumproduct formula. I would want to use wildcard to populate 2 criteria from the data sheet, they are "Ducted Evaporative Cooler Single Stage 1 Tier" and "Ducted Evaporative Cooler Single Stage X-1 Tier". In my formula, I have used a wildcard "Ducted Evaporative * 1 Tier*" for the prior and "Ducted Evaporative * X-1 Tier*" for the later. I would like to know whether by checking my formula that i am using....

View 9 Replies View Related

Sumproduct Formula To Exclude String Values

Jan 22, 2014

Question is...in the below formula the red are pointing back to name ranges in a different tab on my spreadsheet. On that spreadsheet I also have a name range called DTCCSUBSUPPREF

I would like to add within my formula, a string which will point to that name range and exclude values if they fit my criteria. Basically I need to see values only where comp or tri dont show up. comp or tri could be the only value in the cell, or could be in the beginning of a string, at end, or in between. Below examples. I have tried (--(DTCCSUBSUPPREF<>"*comp*")*(--(DTCCSUBSUPPREF<>"*tri*") , and I would have thought the * characters would have served as a catch all.

Below is the entire forumla just in case.

11111111FT04comp
comp
tri
11comp
11tri
tri11111111FRP06

=SUMPRODUCT((--(DTCCSUBTRANSTYPE<>$G$2))*(--(DTCCSUBEVENTTYPE<>$I$2)*(--(DTCCSUBBACKLOAD<>$I$3)
*(--(DTCCSUBINPUTSOURCE<>$I$4)*(--(TEXT(DTCCSUBTRADEDATE,"MMM-YY")=$D$2))))))

View 6 Replies View Related

Evaluate Formula Derived Empty String In SUMPRODUCT?

Dec 10, 2013

I've been trying to get this SumProduct formula to return anything except an error for a while now.

=SUMPRODUCT(('Main Log'!$E$13:$E$17>=$C$2)*('Main Log'!$I$13:$I$17

View 2 Replies View Related

If Text Found In String Return String

Jan 31, 2008

Find a short text string in a column of longer text strings and when that short text string is found return the longer text string that matches.

View 3 Replies View Related

Sumproduct With Value Or Text

Nov 27, 2013

My formula works fine:

In cell D12:
=SUMPRODUCT(('Raw data'!$L$25:$L$10958=Sheet1!$C$9)*('Raw data'!$C$25:$C$10958=Sheet1!$C12),('Raw data'!$H$25:$H$10958))

Sheet1!$C$9 = "Both"

But when i change C9 to i.e. 2013 or 2012....the data does not show anything only 0s?

i have attached a sample : sumprod_values_issue .xlsm‎

View 11 Replies View Related

Sumproduct & Text

Feb 5, 2007

I am trying to use the following formula:
=SUMPRODUCT('Raw Data'!$B$2:$B$10000=$A$1)

The reason why I am using sumproduct is b/c I have to add more to this formula. In column B on the Raw Data sheet there are years listed. The years are formatted as text. Is there a way to have this formula work without having to change the format of column B?

View 10 Replies View Related

Sumproduct For Numbers Within Text

Jan 16, 2014

How to use Sumproduct for the following?

Col1 Col2
5 1 mL
4 1.5 mL
5 2.5 mL
7 2 mL
2 1.5 mL

View 7 Replies View Related

Sumproduct To Find Text

Nov 18, 2008

I recently had a lot of help using the sumproduct formula with isnumber datevalue added to it. I now want to do the same but rather than finding numbers, i want it to find text.

In my attached example. You can see that Jerry A has received three applciations, but 2 of them have been cancelled. How can i add the formula where it would only count Jerry A's applications, but where they have been cancelled i.e. the formula would show 2 not 3.

View 6 Replies View Related

Sumproduct #Value! Error Due To Text

Feb 26, 2007

I'm using this sumproduct and it works until a cell has text.
=SUMPRODUCT(($C7:$C16=$C$2)*($D7:F16))

I've tried =SUMPRODUCT(--($C7:$C16=$C$2),($D7:F16)) but got the #Value! error because of the D7:F16 range. If I changed it to become F7:F16, then it works. But...my original formula adds all values giving me a YTD result. If I changed the range to be only F7:F16, then all I get is just that column's data. I have January to December's data across the columns from D to F. Any ideas on keeping my D to F range? I've tried Transpose but it didn't work either.

View 9 Replies View Related

SUMPRODUCT Contains Text, Using Asterisk

Nov 9, 2009

Let's say I have two columns: Homeless (column A) & Services (column B). Homeless responses can be either "Y" or "N". Service can be "a", "b", or "ab". Here is an example:

Homeless Service
y a
y b
n a
y ab

I was hoping to use SUMPRODUCT to calculate clients who are homeless and receiving "a" service. I want to be able to calculate those receiving "a" service even if they are receiving "b" service, so I attempted to use asterisks like I have used prior with the COUNTIF function. Here is what I thought would work:
=SUMPRODUCT((A2:A5="y")*(B2:B5="*a*"))

And I expected it to return 2, but I found that I couldn't get the SUMPRODUCT to work with asterisks. I am able to get the results I want be creating a new column to sort the services into just those who are homeless and then using COUNTIF, but I'd rather come up with a more streamlined approach.

View 3 Replies View Related

Sumproduct To Return TEXT Value?

Dec 23, 2013

I written the formula below:

=SUMPRODUCT((B7:B9=$A1)*(E7:E9>=0),"CAP EXCEEDED","CAP Not breached")

B7:B9 = text and numerical values in format Year 1, Year 2 etc
E7: E9 = numerical values

what I am trying to achieve is if YEAR 1 for example is equal to cell A1 and the value in column E is greater than 0 then return statement "CAP Exceeded else return the statement CAP not breached.

View 6 Replies View Related

SUMPRODUCT To Return Text

May 1, 2008

The below table is a small extract from my data. I want the formula in C3 to return the contents of A4, that is, where Amount = D3, Policy = E3, Status A3. The following formula returns 0, because I'm trying to return a text string rather than a numerical value:....

View 9 Replies View Related

Wildcard For Text In Sumproduct

Aug 14, 2008

Tring to get the following spread sheet to calc column C using two criteria
Criteria 1 Column D must contain HC Investor anywhere
Criteria 2 Column A must be less than Reimbrusement - 11
The following formula in G2 appears to have an error in criteria 1 ....

View 9 Replies View Related

Text Wildcard In Sumproduct

Jun 15, 2009

I am having great difficulties in finishing this formula off.
It is a sumproduct containg many criterias, however one of them is to only add up a certain coloumn if another column begins with L&L...

The coloumd contains L&L and a number, but the number changes so i need the number to be a wildcard. I have tried ="L&L???" but this does not work.

=-SUMPRODUCT((Catagory="STK Retail")*(MONTH(Inv_Date)=3)*(YEAR(Inv_Date)=2008)*(How"Account")*(Order="L&L???")*(Stock))

View 9 Replies View Related

Sumproduct Using Text And Values

Jan 21, 2004

Is there a way to sum a list that contains both text and values using the SUMPRODUCT function? My efforts yielded the #VALUE! error. SUM and SUMIF will ignore the text but I have multiple criteria.

View 9 Replies View Related

SUMPRODUCT Use To Return A Text

May 11, 2006

If recently learned how to use the sumproduct function as a lookup for multiple criteria and it was working great, but I realize now all the values that I was returning were numbers. Is there a way to use the sumproduct under the same circumstances but to return a text value?

COL ACOL BCOL C
A1X
A2F
A3G
B1E
B2S

I would want the lookup with conditions of ColA=A and ColB=2, so the return should be "F"
=SUMPRODCUT(($A$1:$A$5="A")*($B$1:$B$5=2)*$B$1:$B$5).

View 9 Replies View Related

Extract Text From Given Point In Text String When Data Points Do Not Share Given

Jul 9, 2014

I have a set of task descriptions that I am attempting to trend on. Some of these (Column B) have the customer's name added to the description; others do not. I need to be able to make a list of task names (ColumnA), removing the name from the text string.

The formula I am using is [=LEFT(B3,FIND("for",B3)-2)].

The problem I am having is when the description does not contain the "for" built into the formula, I get "VALUE" error. What adjustment can I make to the formula to pull over the Description if the "for" does not appear in the text string?

View 3 Replies View Related

Search Cell For Text String And Paste Text In Based On Finding?

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

Match Row That Contains Text Text String; List Associated Values From Pivot Table

Mar 28, 2009

refer to the attached workbook for reference. I am looking for a function in Sheet1, Column E that will search for the value of Sheet1, Column A within Sheet2, Column A. When a match is found, the function should look across Sheet2, Columns B - V for values of 1. When such values are found, the function should return the associated value from Sheet2, Row 2. There may be multiple values of 1, and as such, the function should separate values with a comma.

View 3 Replies View Related

Excel 2011 :: Find Multiple Text Strings In Another Text String

Mar 8, 2013

How do I use an Excel formula to find which (if any) multiple sets, each of up to 50 words, exist in a series of rows of a spreadsheet - if set A has one or more words found in a searched cell.

A positive result will return a specific value in the designated result cell. If none of the words in Set A is found in the searched cell, the formulae will repeat the test for the words in Set B, and so on.

After all 50 sets of words have been tested, the formula will move to the next cell in the searched column.

New words will be added to the sets of words continually as required.

Multiple words within sets are included in double quotes. Within each set of words there will be some n-tuples of words (i.e. 24 adjacent words) that contain one or more of the words in the set, but for which the formula will be required to return a negative result. Example: Set A = word 1, word2, word 3, "word1 word2 word3". (The words within a set could also be each entered in separate columns, as opposed to all included in a single cell.) The single column of text to be searched is about 10,000 rows.

I am wanting to use the above in a spreadsheet that contains data downloaded from a series of bank accounts to automatically allocate items of expenditure to one of 20 or so different categories of expenditure.

The formula will search the description field to find words that are used in the in the downloaded files from the various accounts to describe each transaction.

If a word describing travel expenditure (e.g. hotel, "holiday inn" but not "holiday travel") is found in the description of an expenditure item - the item cost will be allocated to the TRAVEL EXPENDITURE column, which is one of 20 or so different categories of expenditure.

Happy to consider a different solution if the task can be done better a different way.

Tried using a combination of INDEX/SEARCH/IF in Excel, but was not able to get a correct result. PS I am using Excel 2011 for Mac - which does not allow macros, so the solution needs to be entirely formula based.

View 1 Replies View Related

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?

View 8 Replies View Related

How To Split Text From Text String Into Separate Columns - No Delimiters

Apr 8, 2014

I have the cell data as below

How would I split into a new column the first part which is a date into a new column, then the country and the remainder into separate columns?

I still want the original data as I need to check that the splits worked well?

16.5.90 CH 1671/90-4
18.10.1991 CH 3056/91-1
24.07.92 ch 2341/92-2
30.7.92 ch 2395/92-3
18.11.92 Us 3533/92-5
26.5.93PCT 1577/93-0
9.8.93 CH 2363/93-8
17.8.93 CH 2445/93-0
25.1.94ch209/94-6;8.12.94ch3714/94-1
25.1.94 ch 209/94-6 ; 8.12.94 ch 3714/94-1
8.4.94 ch 1047/94-0
22.4.94 ch 1255/94-7
18.11.1992 CH 3533/92-5
18.11.1992CH 3533/92-5

View 2 Replies View Related

Compare Multiple Text Cells To One String Of Text And Return Specified Value

May 12, 2012

I have a dataset of shifts and want to compare each shift that needs to be filled to a list of requests for time off (vacation, etc).

For one cell the code is: =IF(ISERROR(SEARCH(B1, A1), 0,1)

Where A1 is the column of requests in comma delimited forme (ie: "AB,CD,EF").

Where B1 is the column containing the person assigned to shift 1 (ie: "AB")

In this case, would return a "1" as no error was returned, as AB was found in the list. Here "1" would represent a schedule conflict.

Without creating many cells for each shift- there are 20 shifts- can I create an array or string together this type of "SEARCH" function?

View 1 Replies View Related

Extract Multiple Text Strings From A Long Text String

Sep 4, 2009

An original text string entry appearing in an Excel cell would be:

"N7C Neuroprotective J5Z Antiviral, other M2Z Antiarthritic, other J5A Antiviral, anti-HIV"

I need to extract N7C, J5Z, M2Z and J5A from this string and list these alphanumeric values in separate cells adjacent to the original text string. The challenge is that these alphanumeric references may appear in different positions within the original string with no fixed value e.g. a "," separating them. The alphanumeric references may also be 3 or 4 characters in length and there may be different numbers of alphanumeric references in the original string.

Another example would be (very different from the first):

"T2Z Recombinant, other K5B Radio-chemoprotective J3Z Antibacterial, other D3A Vulnerary A10C Symptomatic antidiabetic K6Z Anticancer, other R8A Antiasthma B6A Septic shock treatment I1Z Immunostimulant, other S1Z Ophthalmological R8B Antiallergic, non-asthma M1A1 Anti-inflammatory"

You can see that in this further example "A10C" & "M1A1" are 4 character alphanumeric strings wheras the others feature 3 characters.

View 9 Replies View Related

SUMPRODUCT With 2 Wildcard Text Match?

Feb 20, 2014

Here is what I have so far:

=SUMPRODUCT(--(ISNUMBER(SEARCH("Red",A9:A56))),B9:B56,L9:L56)/SUMPRODUCT(--(ISNUMBER(SEARCH("Red",A9:A56))),B9:B56)

So in column A is the text string i.e Big Red Chiquita Apple
Column B is the dollar value
Column C is another variable.

If I want to find "Red" and "Apple" ONLY within same cell. What would be my best bet?

View 5 Replies View Related

SUMPRODUCT Not Working Due To Text In Column

Apr 24, 2009

I'm trying to work out how to fix the formula below to take into account and ignore and text entries, while giving me the result of the sum of column K minus the sum of column J. If I delete the text entries, the code works but I need the text entries to stay where they are. I've attached a sample sheet with fake info to explain whan I'm trying to do.

Cell N28 on the 'MGMT INFO' tab contains the following formula:

=IF(ISERROR(SUMPRODUCT((Sheet01!$K$1:$K$1000)-(Sheet01!$J$1:$J$1000))),0,(SUMPRODUCT((Sheet01!$K$1:$K$1000)-(Sheet01!$J$1:$J$1000))))

Columns J and K on the 'Sheet01' tab contain the Pay and Invoice information for all the work planners for that client that I'm trying to find the difference between. Each work planner has 'Pay' and 'Invoice' also in that column though, one entry per planner which is causing the SUMPRODUCT formula to screw up.

View 3 Replies View Related

SUMPRODUCT With Today() And Text Criteria

Feb 19, 2010

Im attempting to add number of dates within a column (B). I have 2 criteria: Today and person "William". Im using the following and return results is "0"

A: Name
B: Date
C: Type

=SUMPRODUCT(--(Data!$B$2:$B$3100=TODAY()),--(Data!$A$2:$A$3100="William"),--(Data!$B$2:$B$3100))

Once I get this working I would like to adjust for last 30days and look at Type (C) = New

View 5 Replies View Related

Pull From Another Sheet And Sumproduct And TEXT

Oct 16, 2007

I have sheet 1 which has titles set up in column A. These titles are pulled from Sheet 2 (same column). I also have a date listed at the top of sheet 1 (in the same row as dates in sheet 2, but sheet 2 contains multiple columns for each quarter). I would like to set something up where if the date in sheet 1 matches the date in sheet 2 it pulls the corresponding values from the named rows in sheet 1. I tried using sumproduct but its not working b/c it wont take TEXT. Plus I want it to search across all of the columns, not just one.

Below is sheet 1 and I would like to be able to pull 1Q08 values from sheet 2 (which contains the values for every quarter). This is my formula (the first part is the date, the second is row titles, the third is the value (although its only pulling from one column, it s/b pulling from all) :

=SUMPRODUCT((Sheet2!B1:B6500=sheet1!B6)*(Sheet2!A1:A6500=Sheet2!A7)*(Sheet2!BA1:BA6500))

Is there someway to fix this, it would be a huge time saver.
--------------------------------------
1Q08
Revenue
Direct Costs
Gross Profit
Indirect Costs
Stock Based Compensation
Restructure Charge ....................

View 4 Replies View Related

SUMPRODUCT With Cell Reference Rather Than Text

Sep 2, 2008

{=SUM(IF(MONTH('3107'!$A$2:$A$188)=MONTH($A6),'3107'!F$2:F$188))+SUM(IF(MONTH('3207'!$A$2:$A$188)=MONTH($A6),'3207'!F$2: F$188))+SUM(IF(MONTH('4107'!$A$2:$A$188)=MONTH($A6),'4107'!F$2:F$188))+SUM(IF(MONTH('4207'!$A$2:$A$188)=MONTH($A6),'4207 '!F$2:F$188))+SUM(IF(MONTH('4307'!$A$2:$A$188)=MONTH($A6),'4307'!F$2:F$188))+SUM(IF(MONTH('AS21'!$A$2:$A$188)=MONTH($A6) ,'AS21'!F$2:F$188))}

Failed to convert using

=SUMPRODUCT(--((IF(MONTH('3107'!$A$2:$A$188)=MONTH($A6),'3107'!F$2:F$188))+SUMPRODUCT(--(IF(MONTH('3207'!$A$2:$A$188)=MONTH($A6),'3207'!F$2:F$188))+SUMPRODUCT(--IF(MONTH('4107'!$A$2:$A$188)=MONTH($A6),'4107'!F$2:F$188))+SUMPRODUCT(--(IF(MONTH('4207'!$A$2:$A$188)=MONTH($A6),'4207'!F$2:F$188))+SUMPRODUCT(--((IF(MONTH('4307'!$A$2:$A$188)=MONTH($A6),'4307'!F$2:F$188))+SUMPRODUCT(--(IF(MONTH('AS21'!$A$2:$A$188)=MONTH($A6),'AS21'!F$2:F$188)))))))))

A6 is a month column and looks like this "Jan-08"
the '3107', '3207', '4107', etc. are the names of specific sheets, so I'm summing over multiple sheets.

View 9 Replies View Related

Delete Row If Cell Contains Certain Text As Part Of Text String?

May 15, 2014

I have a column of data with letters in each cell, no numerical, only alpha. Now, some of those cells contain the letters "adj sub" as part of the text string in each cell. "Adj sub" is always at the beginning of the text string. As an example, a cell will look like this - "adj sub mhm". I want to delete rows whose cell description does not contain "adj sub" as part of the text in the cell.

View 5 Replies View Related

Loop And Parse All Occurrences Of Text After Text In A String

Nov 29, 2011

I am working on modifying this code (below) that I found here: VB Macro to search webpage for text string

Code:

Option Explicit
Public stResultstr As String
Sub SearchForString()
Dim rngURL As Range
Dim cll As Range
Dim stCheck As String
Dim xmlHttp As Object

[Code]...

So right now I have the script prompt you for a URL range, then it asks you for what you want to search for, and I have it set to return the next 10 characters from that found point.

You can see here:

Code:

If InStr(1, stResult, stCheck, vbBinaryCompare) > intt Then
intt = InStr(1, stResult, stCheck, vbBinaryCompare) + Len(stCheck)

I began to create something to identify the last position of the found item because next I want to loop it to find the next occurance of the same thing and return the next 10 characters after it in the next cell to the right in that same row:

Code:

cll.Offset(, 1).Value = stResultstr

Again, what I want to do here is find and parse out every occurence of a string (inputbox) found on a web page url in column A. The parse occurrences will go to the right in cells C though ? for row N.

View 2 Replies View Related

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

View 7 Replies View Related

Partial Text Lookup In Text String And Return

Mar 15, 2007

Cell H1 has a variable string of references for eg
"FI570783AQ3516346EQ3516346FXVB123456"

I want to return the reference beginiing with "FXVA" so it would ignore the rest and only return FXVB123456 - this should always be 10 characters.

Just to add another complication to the mix, there may be 2 "FXVA" references in the string and i want to get both (these can be in the same string so FXVB123456FXEL123456 - but this might not happen regularly.

View 9 Replies View Related

Check If Text String In Cell With Other Text Is In List

Mar 27, 2008

I have a sheet in which some of the cells have two strings separated by a linefeed. I have come up with a cumbersome formula which will let me check if either of the two strings is a member of a list stored on another sheet. However, it fails if there is only one string in the cell, presumably as there is no linefeed for the formula to find. How can I modify the formula to cope with this situation?

There are also on occasions, three strings in the cell, but I can't seem to access the middle string with the formula. Simplified spreadsheet attached to show the problem. This must be formula-based, as we have a no VBA policy. If you think there is better way of doing this, please let me know.

View 3 Replies View Related

SUMPRODUCT Exclude Numbers And Text Strings?

Apr 19, 2014

I'm trying to sum up the total of a list excluding a list like this:

Column B
012*
019*
1758*
70228
70229
70230
70241

In my worksheet 1, it contains e.g.
Column AC
012-something
012-text
019-jumbo
016-things
70228
70221

Column W contains the numbers I want to add up:
$15
$12
$10
$19
$15
$11

My current formula is:
=SUMPRODUCT(--ISNA(MATCH(Sheet1!AC$4:$AC$157,B2:B7,0)),Sheet1!W4:W157)

It has managed to exclude the numbers (e.g. 70228, 70229, etc) but not the text strings (e.g. 012*) when summing it up

View 2 Replies View Related

Finding Specific Text Within String Of Text?

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

Copy Text Inside Of Text String

Jul 17, 2014

I have one column full of Item #s and Descriptions. I have another column that I want JUST the Item# in it.

For example:

Column 1:
Gyroscope REF#1234 Bike Thing

Column 2 Should Be:
1234

Column 1:
BallWall Bikes Ret# 12456 Helmet Thing

Column 2S hould Be:
12456

I don't know how to do this (I do know how to do VBA mildly, and I am pretty good with formulas). I also have a database of every Item# that could be in that cell.

View 6 Replies View Related

Taking End Text From Long String Of Text And Dot

Jun 14, 2013

I have a bunch of these following below which I need extract text from the first "." from the right hand side.

The amount of text and "." can vary as does the number of characters.

XXXXXXXX.XXXXXX.XXXX.XXX.XXXXX.XXXXXXXXXXXXX.XXXXXXXXXX.Vibrating Screen SC409
XXXXXXXX.XXXXXX.XXXXX.XXXXX.XXXXXXXX.XXXXXXXXXX.Scalping Circuit 4
XXXXXXXX.XXXXXX.XXXXXXX.XXX.XXXX.XXXXX.Fines Stacker ST601

Ultimately i want it to display the following:

Vibrating Screen SC409
Scalping Circuit 4
Fines Stacker ST601

I have used LEFT, RIGHT, SEARCH, LEN functions in various combinations but cant get it to reference the last "." from the right.

View 3 Replies View Related

Removing Text From Middle Of Text String?

Jan 31, 2012

I am trying to remove a middle initial from a text string however not all of the cells have middle initials. In column B I would like to return the first and last name. If needed I can have the first name in column B and the last in column C then combine them.

Example
John A Smith John Smith
John Smith John Smith

View 2 Replies View Related

Extract Numbers After Text In Text String

Mar 12, 2014

I am trying to extract numbers after a specific text in a text string, for eg :abc SN 12345 xyzedf SN No. 456 mnoAs per above, i want to extract any numbers afters "SN". the numbers can be vary in digits i.e. it can be 3 digit numbers or 4 or 6. Also, at times there is some other text in between (like SN No.) numbers and search word (i.e. SN)Any formula to get result as "12345" and "456".

View 5 Replies View Related

ADVERTISEMENT