do you mean this?
This is a discussion on Multiple SUBSTITUTE Functions within the Excel Questions forums, part of the Question Forums category; Is there a way to structure Multiple SUBSTITUTE Functions? I have two values in a text body within a cell ...
Is there a way to structure Multiple SUBSTITUTE Functions? I have two values in a text body within a cell that I want to change, this works...
...however I want to include "Name" (Which is linked to H7) as well.
do you mean this?
Hope this is helpful.
only a drafter,
but broadening my Excel knowledge.
I did some excel training what seems like over ten years ago, and boy have things changed a little
I am trying to make a recipe sort of sheet for my daughters, but im stuck.
in column B I wish to enter g, ts, tb, c, m, and for it to appear as grams, teaspoons, tablespoons, cups, mls
im sure there is a really easy way to make it happen, but alas, I have not found it lol otherwise its a lot of typing for me!
I guess thats it, will thank anyone in advance for a reply or help - THANKS
Welcome to the MrExcel board!
I'm assuming that you want the longer words to actually replace what you type in column B.
(If you didn't need that and would be happy with the full words appearing in, say, column C then you could use a VLOOKUP formula in column C)
A. You could (I'm not recommending this) add AutoCorrect entries to change "c" to "cups" etc. This is simple enough to do BUT would mean "c" changing to "cups" in any column in any spreadsheet or any other Office program until you remembered to go back in and delete those AutoCorrect entries. Way too risky in my mind!
B. Better option, but does require macros to be enabled and your file saved as a macro-enabled file (.xlsm) if using Excel 2007 or later.
This option assumes that column B only gets the g, c etc. That is if you want 3 cups, you would put the "3" in column A and the "c" in column B
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window and test.
If I haven't guessed well enough what you want, post back with more details.Code:Option Explicit Option Base 1 Private Sub Worksheet_Change(ByVal Target As Range) Dim Abbrev Dim changed As Range, c As Range Dim m As Variant Const AbbrevCol As String = "B" Set changed = Intersect(Target, Columns(AbbrevCol)) If Not changed Is Nothing Then Abbrev = Array("g", "grams", "ts", "teaspoons", "tb", "tablespoons", "c", "cups", "m", "mls") Application.EnableEvents = False For Each c In changed m = Application.Match(c.Value, Abbrev, False) If IsNumeric(m) Then If m Mod 2 = 1 Then c.Value = Abbrev(m + 1) End If End If Next c Application.EnableEvents = True End If End Sub
Hope this helps, good luck.
- Read the Posting Guidelines, Forum Rules & FAQs
- Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
- If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker