| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel - Replace differant trailing words or symbyls with key word
How do I replace in multiple cells that start out with walgreen but have
mulitple differant endings to just walgreens. example Walgreens5417 Q03 R 10/06WALGREENS Walgreens5417 Q03 R 10/13WALGREENS |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel - Replace differant trailing words or symbyls with key word
=LEFT(A2,x)
Here x is the length of the word in characters. LEN("Walgreen")=8 HTH Kostis Vezerides On Oct 31, 4:28 pm, working hard at home wrote: How do I replace in multiple cells that start out with walgreen but have mulitple differant endings to just walgreens. example Walgreens5417 Q03 R 10/06WALGREENS Walgreens5417 Q03 R 10/13WALGREENS |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel - Replace differant trailing words or symbyls with key w
Sorry I dont get it?
if 8 = charactors. Would it not be replace with: walgreens(8) what is (A2 Thanks Peter "vezerid" wrote: =LEFT(A2,x) Here x is the length of the word in characters. LEN("Walgreen")=8 HTH Kostis Vezerides On Oct 31, 4:28 pm, working hard at home wrote: How do I replace in multiple cells that start out with walgreen but have mulitple differant endings to just walgreens. example Walgreens5417 Q03 R 10/06WALGREENS Walgreens5417 Q03 R 10/13WALGREENS |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel - Replace differant trailing words or symbyls with key word
If the first number after desired text always begins with a digit, you
can use this array formula: =LEFT(F9,MATCH(TRUE,ISNUMBER(--MID(F9,ROW(INDIRECT("1:"&LEN(F9))),1)), 0)-1) Assumes your text is in F9. After pasting this formula in the formula bar, press Ctrl+Shift+Enter to confirm (instead of regular Enter). On Oct 31, 10:28 am, working hard at home wrote: How do I replace in multiple cells that start out with walgreen but have mulitple differant endings to just walgreens. example Walgreens5417 Q03 R 10/06WALGREENS Walgreens5417 Q03 R 10/13WALGREENS |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel - Replace differant trailing words or symbyls with key w
A2 is the address of the cell that contains the string:
Walgreens5417 Q03 R 10/06WALGREENS Then try: =left(a2,8) in another cell (say B2???) working hard at home wrote: Sorry I dont get it? if 8 = charactors. Would it not be replace with: walgreens(8) what is (A2 Thanks Peter "vezerid" wrote: =LEFT(A2,x) Here x is the length of the word in characters. LEN("Walgreen")=8 HTH Kostis Vezerides On Oct 31, 4:28 pm, working hard at home wrote: How do I replace in multiple cells that start out with walgreen but have mulitple differant endings to just walgreens. example Walgreens5417 Q03 R 10/06WALGREENS Walgreens5417 Q03 R 10/13WALGREENS -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel - Replace differant trailing words or symbyls with keyw
Your question is ambiguous, give before and after example.
working hard at home wrote: Sorry I dont get it? if 8 = charactors. Would it not be replace with: walgreens(8) what is (A2 Thanks Peter "vezerid" wrote: =LEFT(A2,x) Here x is the length of the word in characters. LEN("Walgreen")=8 HTH Kostis Vezerides On Oct 31, 4:28 pm, working hard at home wrote: How do I replace in multiple cells that start out with walgreen but have mulitple differant endings to just walgreens. example Walgreens5417 Q03 R 10/06WALGREENS Walgreens5417 Q03 R 10/13WALGREENS |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel - Replace differant trailing words or symbyls with key w
This is Vertical list with multiple differant prefex.I want to eliminate the
back end info words just keep front wording, using find and replace tool and not list each sell as their are hundreds example Walgreens5417 Q03 R 10/06WALGREENS Walgreens5417 Q03 R 10/13WALGREENS Become Walgreens "iliace" wrote: If the first number after desired text always begins with a digit, you can use this array formula: =LEFT(F9,MATCH(TRUE,ISNUMBER(--MID(F9,ROW(INDIRECT("1:"&LEN(F9))),1)), 0)-1) Assumes your text is in F9. After pasting this formula in the formula bar, press Ctrl+Shift+Enter to confirm (instead of regular Enter). On Oct 31, 10:28 am, working hard at home wrote: How do I replace in multiple cells that start out with walgreen but have mulitple differant endings to just walgreens. example Walgreens5417 Q03 R 10/06WALGREENS Walgreens5417 Q03 R 10/13WALGREENS |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel - Replace differant trailing words or symbyls with key w
I would use =LEFT(B2,9)
To return walgreens.........9 chars Gord On Wed, 31 Oct 2007 11:09:24 -0500, Dave Peterson wrote: A2 is the address of the cell that contains the string: Walgreens5417 Q03 R 10/06WALGREENS Then try: =left(a2,8) in another cell (say B2???) working hard at home wrote: Sorry I dont get it? if 8 = charactors. Would it not be replace with: walgreens(8) what is (A2 Thanks Peter "vezerid" wrote: =LEFT(A2,x) Here x is the length of the word in characters. LEN("Walgreen")=8 HTH Kostis Vezerides On Oct 31, 4:28 pm, working hard at home wrote: How do I replace in multiple cells that start out with walgreen but have mulitple differant endings to just walgreens. example Walgreens5417 Q03 R 10/06WALGREENS Walgreens5417 Q03 R 10/13WALGREENS |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel - Replace differant trailing words or symbyls with key w
Maybe the OP just wanted one store <vbg.
Gord Dibben wrote: I would use =LEFT(B2,9) To return walgreens.........9 chars Gord On Wed, 31 Oct 2007 11:09:24 -0500, Dave Peterson wrote: A2 is the address of the cell that contains the string: Walgreens5417 Q03 R 10/06WALGREENS Then try: =left(a2,8) in another cell (say B2???) working hard at home wrote: Sorry I dont get it? if 8 = charactors. Would it not be replace with: walgreens(8) what is (A2 Thanks Peter "vezerid" wrote: =LEFT(A2,x) Here x is the length of the word in characters. LEN("Walgreen")=8 HTH Kostis Vezerides On Oct 31, 4:28 pm, working hard at home wrote: How do I replace in multiple cells that start out with walgreen but have mulitple differant endings to just walgreens. example Walgreens5417 Q03 R 10/06WALGREENS Walgreens5417 Q03 R 10/13WALGREENS -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel - Replace differant trailing words or symbyls with key w
"Multiple differant prefex". If a number always follows a prefix, I
reiterate my previous solution. On Oct 31, 12:07 pm, iliace wrote: If the first number after desired text always begins with a digit, you can use this array formula: =LEFT(F9,MATCH(TRUE,ISNUMBER(--MID(F9,ROW(INDIRECT("1:"&LEN(F9))),1)), 0)-1) Assumes your text is in F9. After pasting this formula in the formula bar, press Ctrl+Shift+Enter to confirm (instead of regular Enter). On Oct 31, 10:28 am, working hard at home wrote: How do I replace in multiple cells that start out with walgreen but have mulitple differant endings to just walgreens. example Walgreens5417 Q03 R 10/06WALGREENS Walgreens5417 Q03 R 10/13WALGREENS- Hide quoted text - - Show quoted text - On Oct 31, 12:39 pm, working hard at home wrote: This is Vertical list with multiple differant prefex.I want to eliminate the back end info words just keep front wording, using find and replace tool and not list each sell as their are hundreds example Walgreens5417 Q03 R 10/06WALGREENS Walgreens5417 Q03 R 10/13WALGREENS Become Walgreens "iliace" wrote: If the first number after desired text always begins with a digit, you can use this array formula: =LEFT(F9,MATCH(TRUE,ISNUMBER(--MID(F9,ROW(INDIRECT("1:"&LEN(F9))),1)), 0)-1) Assumes your text is in F9. After pasting this formula in the formula bar, press Ctrl+Shift+Enter to confirm (instead of regular Enter). On Oct 31, 10:28 am, working hard at home wrote: How do I replace in multiple cells that start out with walgreen but have mulitple differant endings to just walgreens. example Walgreens5417 Q03 R 10/06WALGREENS Walgreens5417 Q03 R 10/13WALGREENS- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| eliminating trailing zeroes imported into word document from excel file | Excel Discussion (Misc queries) | |||
| IF formula in Excel / Replace numbers with 'words' | Excel Discussion (Misc queries) | |||
| Can excel underline misspelled words like Word? | Excel Discussion (Misc queries) | |||
| Replace a long list of abbreviations with full words in Excel | Excel Worksheet Functions | |||
| Excel to replace words ? | New Users to Excel | |||
Linear Mode
