Formula To Find And Substitute A Word In Text If Condition Met
Aug 18, 2014
In cell A1, I have three possible text strings: (1) "change/s: changed the color green to red", (2) "change/s: changed the color from green to red, changed the size from big to small", or (3) "changed the color from red to green".
I need a formula that will look at a text string, and if there is a comma in the string, it finds the word "change/s" and substitutes it with "changes", if it finds no comma, it substitutes the word "change/s" to "change", and if the word "change/s" is not in the text string at all, it leaves that text string unchanged.
View 4 Replies
ADVERTISEMENT
Feb 24, 2014
I want to change country name 'California' and 'Belziuma' with new country name as 'USA' in column B2.
Similarly change country name 'Moscow' with new name 'Russia'.
If cond not working.
View 1 Replies
View Related
May 23, 2006
I am having trouble getting my IF statement to test if the cell contains the text "sale" return "X" if not "Y". I need it to search through the text string in that cell and find a certain word, and if it finds that word, retrn a value. I am really having difficulty with is what symbol or function do I use for the logical test? (i.e. =, <>, MATCH, INDEX?)
View 4 Replies
View Related
May 25, 2007
Got the following formula:-
=LEFT(L5, FIND("(",L5)-1)
I need to add the SUBSTITUTE function to this but can't figure out where it goes if somebody could point me in the right direction please? My substitute formula is SUBSTITUTE(L5,"car","train").
View 4 Replies
View Related
Aug 28, 2012
Is there a formula to find a specific word through many columns? Between A1 and L4500 i got a lot of diffrent information. I want to show if there is any cells in the row with the specific text, a formula in column M. Etc. If C100 got "topside" a notice in column M
View 5 Replies
View Related
Aug 6, 2014
I have a list of skills listed as codes in column A. I would like to write in a separate column, B, the name of the skill based on the fact that A contains a specific text. Only the first condition return a correct value, the others condition returns #VALUE! as if they are not satisfied.
View 5 Replies
View Related
May 8, 2014
I have three columns of Data. A is vehicle number, B is miles and C is a Date. I want to be able to pull the miles for the newest date when the user types in a bus number next to the formula. I think It can be done with an array formula but I am not 100% sure on how to do it. Also is it possible if they enter a vehicle number and a date that a different formula finds the miles for the most recent date to the date entered.
Excel Help.JPG
View 6 Replies
View Related
Jan 11, 2014
I would like to take a string such as R0-H6-D2 and return a number (1-4) based on one of four values for R, H and D in a separate column for each. The attachment should make what I am trying to do clearer.
Substitute.png
View 7 Replies
View Related
Feb 9, 2009
I have a text field (description) and in the description i have a product code S followed by 7 digits and then in the process of pasting into excel i have lost the space after this code and before the next text. E.g. "Ballpoint pen S1234567With Free Delivery" should be "Ballpoint pen S1234567 With Free Delivery".
I dont know how to say =if("S" followed by 7 numbers,subsitute ..... etc)
I understand how to use IF and substitute. its the 7 numbers part i am stuck on.
I could do it in access with the wildcards but excel is different.
View 14 Replies
View Related
Mar 25, 2014
I have a text variable MtgDate containing "25/03/2014"
I need to produce another text variable (to build into a file name) MtgDate1 with the text "2014-03-25"
I was going to use the Substitute function to replace the "/" with a "-" then Mid to juggle the dd-mm-yyyy to yyyy-mm-dd but at the moment I can't even find the right syntax for the Substitute.
Got as far as:
[Code] .....
but this just sets MtgDate1 as "=Substitute(MtgDate, " / ", " - ") ie reduces the double quotes to single ones.
View 2 Replies
View Related
Oct 2, 2008
I have data coes that need to be converted, basically need to remove 1st and 12th digits, 12th digit only, or 11th digit. I have built spreadsheet with a mid sub formula to do all 3 separately, but cannot figure out how to combine the formula to do all three.
I am attaching the spreadsheet,
View 11 Replies
View Related
Feb 11, 2005
I'd like to use VBA to create a super substitute function. For my needs, nesting is insufficient because my substitution list is at 20 and growing. To make matters worse, the function needs to be used in several places.
What I'd like to do is have a named table with two columns for the function to use as a look-up for potential substitutions. The first column would contain the original text and the second would contain the replacement text. This way, whenever new items come up, all I have to do is add them to the list. The syntax of the function would be along the lines of SUPERSUB(TextString, table), where TextString contains the text that could be modified.
View 9 Replies
View Related
Oct 2, 2012
I've in cell A1 an entry like this: 123*456*7890
=SUBSTITUTE(A1) in cell 'A2' gives me: 1234567890
=ISTEXT(A2) in cell 'A3' gives me: TRUE
BUT, =A3+1 gives me: 1234567891
Hows that happening? Substitute function gives me the output which is a TEXT, and how is it that when I add 1 to it, I get an answer? Shouldn't I get a #VALUE! error instead?
View 2 Replies
View Related
May 17, 2014
I've got a workbook with multiple formulas e.g.:
t=S/D
TCh=Ch*(S^2/2D)*(D/Q)
TC=Co*(D/Q)+Ch*((i-D)/2i)*Q
which are built by implementing the symbols:
t, S, D, TCh, Ch, Q, TC, Co, i
I'd like to substitute the symbols in following sequence:
t = a
S = b
D = c
TCh = d
Ch = e
Q = f
TC = g
Co = h
i = x
Questions:
1) Which formula could substitute the symbols automaticly?
2) If there are up to 100 formulas in the workbook in the range of A1:E100,-
would it be possible to to set the substitution for the mentioned range?
See the attachment.
View 5 Replies
View Related
Nov 17, 2011
Is this possible because when i try to use the Substitute formula my Concatenate formula is doesn't work. This data in cell A1 is linked from another sheet
PHONE
WIRE
CANDY
INDIA
When I use the Concatenate formula only, it works fine but when i try to add the Substitute to this, it looks like this
PHONE WIRE CANDY INDIA
I want it to look like this, removing any blank lines within cell A1
PHONE
WIRE
CANDY
INDIA
Something wrong with the formula? how come it doesn't do both?
=SUBSTITUTE(CONCATENATE(A1),CHAR(10),"")
View 1 Replies
View Related
Jun 11, 2013
I currently have a large amount of data extracted from a performance tracking system. The columns I am concerned with are Page Title and Time (the time it takes the system to navigate to the this page).
I am trying to create a dashboard to show:
Webpage Title (each unique instance of the webpage)Aggregate Time (total time it takes for all instances)# of Hits (Each time the value appears in the data)Average Time (this formula I can handle; Aggregate Time/# of Hits)
The major issue is that the webpage is extracted as XYZ001 -- PAGE1, XYZ439 -- PAGE2, XYZ854 -- PAGE1, etc. Basically, I am only concerned with what comes after the --.
Here is an example of the two tabs.
DASHBOARD TAB:
Page Title
Aggregate Time
# of Hits
Avg. Time
PAGE1
[Code]...
Basically, I am looking for a formula that says,
For any instance in the Page Title column that contains PAGE3, add the amount in the Time column (i.e., 1.2 +.5).
View 3 Replies
View Related
May 7, 2014
I have a long list of process steps in a collumn e.g.
A
_Tank1_CIP
_Tank1_CIP
_Tank2_CIP
_Tank4_CIP
_Tank_9_CIP
and then i have a list of tanks: Tank1, Tank2 etc. The i want a forumla to extract and return the tank in a adjacent cell:
A B
_Tank1_CIP Tank1
_Tank1_CIP Tank1
_Tank2_CIP Tank2
_Tank4_CIP Tank4
_Tank_9_CIP Tank9
View 6 Replies
View Related
Feb 14, 2009
i want a function that tell me how many duplicates are in the ID for a particular text.
View 9 Replies
View Related
Jun 9, 2009
Have problems using find and the Dictionary
What Im trying to do is find a certain word in a string then return the number associated with that word
View 7 Replies
View Related
Mar 14, 2014
I would like to have a formula find a specific word in column L and return the sum from column E for the same fund from column C.
I tried using =SUMIF(L:L,"*annual*",E:E) but that gives me the sum of annual for the entire column. I need to be able to specify the fund.
word "annual" also appears in "semi annual" so I need to be able to separate the two.
View 5 Replies
View Related
Feb 28, 2007
I have a workbook with about 8 sheets that is used for pricing vehicles with options.
On the first sheet is the list of about 40 vehicles. I would like to insert a command that IF the quantity of vehicle X = 1 (all others would be blank) then insert a MS Word document that contains the proposal.
The MS Word document could be a worksheet if necessary.
View 14 Replies
View Related
Dec 20, 2012
I am looking for a way of creating the following conditioned concatenation.
I have two tables, let's call them "summary" and "detailed".
The "detailed" table is something like the following:
ID
VOL
001
01
001
05
[code]....
The "summary" table below gets info from the "detailed" table. The 'ID'is now unique. I'm looking for a formula on the 'VOL (concatenated)' column cells it should get all rows from the "detailed" table with the same ID and then concatenate the 'VOL' column results, comma separated:
ID (unique)
VOL (concatenated)
001
V01, V03, V05
002
V01, V04
003
V06
PS: I have people using this table with office 2003, so compatibility is necessary...
View 1 Replies
View Related
Jan 7, 2014
I have an existing Cash Flow Report that has a column of abbreviated/shortened (WBS Element) title where each cell contains a unique three lettered/numbered amount of characters (Example: 200). These three abbreviated character cells are specific and relate to their full/longer (SAP WBS Element) title (Example: WBS DWRRI-BW066-200).
In my attached excel model (Find, Locate, and Align WBS Elements to Cash Flow Report.xlsx), I need a formula for the, "Cash Flow Report WBS Elements" sheet in cells B3 thru B11 that will look at the abbreviated three lettered/numbered (WBS Element) titles in cells C3 thru C11, then search and recognize its unique counterpart contained in the, "SAP WBS Elements Export" sheet and return this full/longer (SAP WBS Element) title to the, "Cash Flow Report WBS Elements" sheet in cells B3 thru. B11, just to the left of its abbreviated/shortened (WBS Element) title.
View 3 Replies
View Related
Jan 7, 2014
I have an existing Cash Flow Report for my work that has a column of abbreviated/shortened (WBS Element) titles where each cell contains unique three lettered/numbered characters (Example: 200). These three abbreviated character cells are specific and relate to their full/longer (SAP WBS Elements) titles (Example: WBS DWRRI-BW066-200).
In my attached excel model (Find, Locate, and Align WBS Elements to Cash Flow Report.xlsx), I need a formula for the, "Cash Flow Report WBS Elements" sheet in cells B3 thru B11 that will look at the abbreviated three lettered/numbered (WBS Element) titles in cells C3 thru C11, then search and recognize its unique counterpart contained in the, "SAP WBS Elements Export" sheet and return this full/longer (SAP WBA Element) title to the, "Cash Flow Report WBS Elements" sheet in cells B3 thru B11, just to the left of its abbreviated/shortened (WBS Element) title.
Find, Locate, and Align WBS Elements to Cash Flow Report.xlsx
View 1 Replies
View Related
Mar 4, 2014
I have a worksheet with several columns. I need a formula to search column D only and each time a specific location is identified to replace that location with alternate text. Example (ORIGNAL TEXT):
Column Dtext to text help.xlsx
BIRD
FISH
DOG
BAT
BUG
I need to search that listing and each time the word BIRD is mentioned have it replaced with FEATHERS and each time DOG is listed have it replaced with TAILS Final result would look like:
FEATHERS
FISH
TAILS
BAT
BUG
All other text should stay the same and replacement text should appear in the cell of the text it is replacing. This is a sheet used by multiple people several times a day and so the Find/Replace option really won't work.
Have working on this for at least 6 months and it just isn't going to happen for me. I thought I could use a Conditional format, but that is producing no results either.
View 3 Replies
View Related
Jun 23, 2014
I am trying to use SUMIFS function in Excel 2010 to add the amount of money spent on a certain category by year. I have the year part of the calculation worked out, but can not seem to get Excel to find a word in a cell as part of the last condition. So far I have:
=SUMIFS(d2:d131,(--(YEAR($A$2:$A$131)=B145)),(g2:g131,"*Maine*"))
The A column contains dates, the D column includes the values to be totaled. The G column may include "utility bill for Maine" or "upkeep for Maine" or other text. I want excel to use having the word Maine in column G to be included in the total and those without the word to be omitted.
View 7 Replies
View Related
Sep 23, 2007
i have spent 40 hours, and still didn't find a solution. Please help is need it!!
example:
i have to find all articles with same code (222). first one has Q =100 second one Q=250.
soled Q=150
(i am talking about 5000 rows, 400 different or same articles per month- 12 months)
columnA-----columnB---columnC
222 ----------100---------0
111-----------50
333-----------70
222----------200---------200
333---------- and so on
first i have to deduct from the first one it finds (max. till 0 ...it can not be negative) ....after finding another one it deducts the rest---it means 50
Is there any kind of formula with this possibility.
if it is poorly writen please let me know for more info.
i am not an expert in excell, but i have tried variations of sumif, vlookup functions, but i always get stuck deducting the whole Quantity from all of the same (222) articles .
View 9 Replies
View Related
Jun 12, 2007
I am using this formula:
=IF($A27="","",IF((OR(+J27>P$10,+J27
View 9 Replies
View Related
Jul 9, 2014
How can i find the last matched row of a given text
So say i wanted to search Jim and i had in range a1:a10 - would need to retrieve 4 as the last Jim found is in ROW 4
Jim
Harry
Jim
Jim
Harry
H
H
H
H
Harry
View 1 Replies
View Related
Jul 18, 2007
What formula can I use to search a range of cell values for a specific text string? My method is searching formulas instead, which is not what I want.
Background: I am developing a little test script for a project and I want to include some automatic validations to help make the job of the testers easier.
What I have set up so far:
1. Testers input numbers into specific fields M8 through M20.
2. Fields N8 through N20 automatically check fields M8 through M20 and compare those with what is contained in a range of hidden cells I set up in advance. A formula ensures that it reports "correct" or "incorrect" on a field-by-field basis, based on the comparison.
3. Cell O8 searches for the text string "incorrect" in cells N8 through N20. If that text string appears in any one of the cell values (not formulas), then O8 directs the tester to test again. If the text string "incorrect" does not appear, then cell O8 reports that the test was successful, and the tester can proceed to the next test (exact same setup repeats).
My only problem is in the formula used for the last step. I have tried a few different formulas I know in cell 08, but none are giving me the results I'm looking for. The closest I have come is a successful search and validation based on the string being in the formula instead of in the value of the given cell.
View 9 Replies
View Related
