Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove any letter from a referenced cell
Hi Mike,
This sounds very promising, but I only have "user form", "module" and "class module" in the "insert" sub-menu available when I right-click on 'this workbook'... Which one should I choose or what should I do to get to where you think I should be??? Thanks. "Mike H" wrote: Hi, Alt + f11 to open VB editor. Right click 'this workbook' insert function and paste this in Function extractthings(rng As range) Set RegExp = CreateObject("vbscript.RegExp") With RegExp .Global = True .Pattern = "[\d\-\&]" End With Outstring = "" Set Collection = RegExp.Execute(rng) For Each RegMatch In Collection Outstring = Outstring & RegMatch extractthings = Outstring Next End Function then in b1 insert the formula =extractthings(a1) Mike |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove any letter from a referenced cell
apologies, it's insert MODULE and paste it in there
Mike "FiluDlidu" wrote: Hi Mike, This sounds very promising, but I only have "user form", "module" and "class module" in the "insert" sub-menu available when I right-click on 'this workbook'... Which one should I choose or what should I do to get to where you think I should be??? Thanks. "Mike H" wrote: Hi, Alt + f11 to open VB editor. Right click 'this workbook' insert function and paste this in Function extractthings(rng As range) Set RegExp = CreateObject("vbscript.RegExp") With RegExp .Global = True .Pattern = "[\d\-\&]" End With Outstring = "" Set Collection = RegExp.Execute(rng) For Each RegMatch In Collection Outstring = Outstring & RegMatch extractthings = Outstring Next End Function then in b1 insert the formula =extractthings(a1) Mike |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove any letter from a referenced cell
Thank you so much Mike!
I've been wondering for a few years now how to create a function in Excel and cursing at the impossibility to use regular expressions. You have way more than answered my question: you also showed me possibilities I was eager to know but didn't know where to look for and how to get started. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove any letter from a referenced cell
Your welcome.
I noticed after you had included the space in your required output. If you do want spaces output then change the pattern to ..Pattern = "[\d\-\&\?\"" ""]" Mike "FiluDlidu" wrote: Thank you so much Mike! I've been wondering for a few years now how to create a function in Excel and cursing at the impossibility to use regular expressions. You have way more than answered my question: you also showed me possibilities I was eager to know but didn't know where to look for and how to get started. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove any letter from a referenced cell
Perhaps a bit off-subject, but it's my thread so I guess I can do what I want
from it (can't I?)... How would you cut out anything beyond the first set of, say numbers, in say the following string (?): aBc123Def45 (from the preceding I would like to get "123" only, and drop "45") Is it too much asking?? "Mike H" wrote: Your welcome. I noticed after you had included the space in your required output. If you do want spaces output then change the pattern to .Pattern = "[\d\-\&\?\"" ""]" Mike "FiluDlidu" wrote: Thank you so much Mike! I've been wondering for a few years now how to create a function in Excel and cursing at the impossibility to use regular expressions. You have way more than answered my question: you also showed me possibilities I was eager to know but didn't know where to look for and how to get started. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove any letter from a referenced cell
Found it!
Function trimfuss(rng As Range) Set RegExp = CreateObject("vbscript.RegExp") With RegExp .Global = True .Pattern = "\d{1,}" End With Outstring = "" Set Collection = RegExp.Execute(rng) For Each RegMatch In Collection Outstring = Outstring & RegMatch trimfuss = Outstring Exit For Next End Function "FiluDlidu" wrote: Perhaps a bit off-subject, but it's my thread so I guess I can do what I want from it (can't I?)... How would you cut out anything beyond the first set of, say numbers, in say the following string (?): aBc123Def45 (from the preceding I would like to get "123" only, and drop "45") Is it too much asking?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to remove a letter from one cell to another | Excel Discussion (Misc queries) | |||
remove first letter | Excel Worksheet Functions | |||
The absolute referenced cell does not move when source cell moves | Excel Worksheet Functions | |||
See where a cell is referenced | New Users to Excel | |||
How do I double click a cell and jump to cell's referenced cell | Excel Discussion (Misc queries) |