#### Substitute each instance of "B"in a range with value in Col A

```need to substitute each instance of "B" in a range B4..g24 with the value in
the Corresponding row, Col A.
--
Al
```
 0
AMK (4)
6/12/2005 9:46:01 PM
excel 39879 articles. 2 followers.

5 Replies
292 Views

Similar Articles

[PageSpeed] 17

```You could use a formula like

=SUBSTITUTE(UPPER(B4),"B",\$A4)

if you want "B" and "b" replaced if you only want "B" replaced

=SUBSTITUTE(B4,"B",\$A4)

copy across five columns, then down, once done you can copy and paste
special as values and replace the old table

--
Regards,

Peo Sjoblom

"AMK" <AMK@discussions.microsoft.com> wrote in message
news:35BC9A2F-1BFF-4568-AF73-7260E2476212@microsoft.com...
> need to substitute each instance of "B" in a range B4..g24 with the value
> in
> the Corresponding row, Col A.
> --
> Al

```
 0
terre081 (3244)
6/12/2005 9:55:58 PM
```And maybe one more variation?

=SUBSTITUTE(SUBSTITUTE(B4,"B",UPPER(\$A4)),"b",LOWER(\$A4))

Peo Sjoblom wrote:
>
> You could use a formula like
>
> =SUBSTITUTE(UPPER(B4),"B",\$A4)
>
> if you want "B" and "b" replaced if you only want "B" replaced
>
> =SUBSTITUTE(B4,"B",\$A4)
>
> copy across five columns, then down, once done you can copy and paste
> special as values and replace the old table
>
> --
> Regards,
>
> Peo Sjoblom
>
>
> "AMK" <AMK@discussions.microsoft.com> wrote in message
> news:35BC9A2F-1BFF-4568-AF73-7260E2476212@microsoft.com...
> > need to substitute each instance of "B" in a range B4..g24 with the value
> > in
> > the Corresponding row, Col A.
> > --
> > Al

--

Dave Peterson
```
 0
ec357201 (5290)
6/13/2005 1:29:10 AM
```Thanks but neither solution appears to work. I need to exchange each instance
of "B" in the Range with the Value in Col A same Row as the "B".

"AMK" wrote:

> need to substitute each instance of "B" in a range B4..g24 with the value in
> the Corresponding row, Col A.
> --
> Al
```
 0
AMK (4)
6/13/2005 5:02:01 AM
```You'll need the same formula for each cell in that range.

If you put the formula you want in column H4 and drag to M4, then select H4:M4
and drag to row 24, it might just work ok.

AMK wrote:
>
> Thanks but neither solution appears to work. I need to exchange each instance
> of "B" in the Range with the Value in Col A same Row as the "B".
>
> "AMK" wrote:
>
> > need to substitute each instance of "B" in a range B4..g24 with the value in
> > the Corresponding row, Col A.
> > --
> > Al

--

Dave Peterson
```
 0
ec357201 (5290)
6/13/2005 11:36:29 AM
```Yes, this works just fine. I didn't quite understand it before. Thanks

"Dave Peterson" wrote:

> You'll need the same formula for each cell in that range.
>
> If you put the formula you want in column H4 and drag to M4, then select H4:M4
> and drag to row 24, it might just work ok.
>
> AMK wrote:
> >
> > Thanks but neither solution appears to work. I need to exchange each instance
> > of "B" in the Range with the Value in Col A same Row as the "B".
> >
> > "AMK" wrote:
> >
> > > need to substitute each instance of "B" in a range B4..g24 with the value in
> > > the Corresponding row, Col A.
> > > --
> > > Al
>
> --
>
> Dave Peterson
>
```
 0
AMK (4)
6/13/2005 3:11:06 PM

Similar Artilces:

Grouping variable length values
I have a field called contractnum which contains a variable-length string from 8 -17 characters. My difficulty is that when I sort them, I would like to group them based on on the section of digits just before the last. The 6 entries below would be grouped as described. My problem is differnet lengths in various entries. The last digit in some entries may represent a change to the original. For example, 123 and P123555 both represent original documents and the last digits represent changes to those. 123 (=original number) 123 1 (a modification to the original 123) 123 2 (a second mod...

Building Chart based on Column Value
I would like my chart to show rows where "COLUMN C" = "TEAM1". Then I can change "TEAM1" to "TEAM2" and only chart rows where "COLUMN C" = "TEAM2". Does anyone know how to do this? Thanks! Greg Hi Greggy, There is a sample here http://www.edferrero.com/ReportWorkbook.zip (Might be a bit more than what you want, but the techniques are the same) Ed Ferrero http://www.edferrero.com >I would like my chart to show rows where "COLUMN C" = "TEAM1". Then I > can change "TEAM1" to "TEAM2" a...

Find value in a column and insert rows above
The set up looks like this: ColU ColV ColW ColX Y N N N Y N N N N Y N N N N Y N N N Y N N N Y N N N Y N N N Y Y Columns will always be U through X and will always be sorted in this order. I need to find the first Y in each column and insert 2 rows above that row. On the blank row above the first Y, I need to highlight in yellow and put title in the first cell, such as New, Old, Existing, Deleted. Any help would be greatly appreciated. Thanks for your time, Dee If desired, send your file to my address below. I will only look if: 1. You send a copy of this ...

Can you expand/collapse columns based on a reportitem/field values
I have a report that is driven by accounting periods. The columns are 1, 2, 3, Q1, 4, 5, 6, Q2...YTD. Currently when the report opens all that is shown is Q1, Q2, Q3, Q4, and YTD and the user can expand from there. What I want to happen is, if we are in Q2, I want columns 4,5,6, Q2 to be expanded. So the report should read Q1, 4,5,6, Q2, Q3, Q4, YTD. Is this possible? Thanks in advance Original Stealth, Certainly can. I created the following dataset: SELECT 'Q1' AS Expr1, 1 AS Expr2, 500 AS Expr3 UNION ALL SELECT 'Q1' AS Expr1, 2 AS Expr...

SQL to get the lowest unique value from table
Hi I'm looking for some SQL to get the lowest unique value in a table: eg, <table> ID BID 1 1 2 1 3 1 4 2 5 2 6 2 7 3 8 4 3 is the lowest unique value. I would need to return the ID. Any suggestions? thanks Lee "Lee" <lee@digital-interactive.com> wrote in message news:a981d263-7f22-4b14-8493-014559da8590@z41g2000yqz.googlegroups.com... > Hi > I'm looking for some SQL to get the lowest unique value in a table: > eg, > > <table> > > ID BID > 1 1 > 2 1 > 3 1 > 4 2 > 5 2 > 6 2 ...

Pivot Table Data Ranges
I just upgraded to excel2000, and am finding out that keyboard shortcuts used in 97 do not exist in 2000. Does anyone know of a KEYBOARD shortcut to modify, expand and or create data ranges in the dialog box for entering the range for the pivot. I have many data 'lists' which are added to each month, and when I want to update the corresponding pivot table, I used to simply hit "shift" + "end" + "down arrow" to automatically go to the last populated cell in the list. In 2000, I either have to enter in the cell addy's manually or use the mouse...

Column A to change font color if column B is not empty
Hi, I'd like to have column A of a sheet to automatically turn to red font when a cell in column B is entered,ie, the Cell A3 will change from red to black when B3 is entered and B3 is a date. Can it be done? Thanks & Regards, Val Hi try: - select cells A3:A10 - goto 'Format - Conditional format' - enter the formula =\$B3<>"" - choose a format -- Regards Frank Kabel Frankfurt, Germany dolphinv4 wrote: > Hi, > > I'd like to have column A of a sheet to automatically > turn to red font when a cell in column B is entered,ie, > the Cell ...

Repeat values in column A a certain number of times depending on the value in column B
Imagine a set of data as set out below: Column A Column B Apples 24 Pears 36 Oranges 8 I want to poplulate a column (for exampel column A on a new sheet), where the values in column A will be repeated as many times as the value in column B Thus the first 24 rows will say Apples, the next 36 rows will Pears, Oranges. I need a formula that recogonizes that when it gets to row 25 that it should no longer need to copy Apples, but then switch to pears. This might sound like an unusual request but if I can grasp a way to do this I can create the table I need and populate the rest...

=?utf-8?B?V2FudCB0byByZXBsYWNlIHRoZSBzcGVjaWFsIGNoYXIgKOKAoikgd2hpbGUgcmVhZGluZyB4bWwgYnkgWG1sVGV4dFJlYWRlciBDbGFzcw==?=
hi, i am using XmlTextReader class to read xml from http link ,now my problem is that i want to replace the special characters(•) from xml before reading its tag, coz it is giving the error in XmlTextReader reader.Read() method. Please suggest any solutions to this. Thanks in advance, Ashish ashu2409 wrote: > i am using XmlTextReader class to read xml from http link ,now my problem is > that i want to replace the special characters(•) from xml before reading > > its tag, coz it is giving the error in XmlTextReader reader.Read() method. > > Please suggest any solu...

=?Utf-8?B?5LiL6LyJ5YWN6LK755qE5Y+N55eF5q+SIDMxNTk=?=

Calculating greatest number in a range
This is a little tricky to explain, but hopefully will make sense. A series of data is produced that monitors concurrent users on a system. This is returned every five minutes of every day. This is on a work book which has a month for each tab. Data typically comes in the following, simple form: Wed Nov 26 10:50:01 GMT 2008 0 Wed Nov 26 10:55:00 GMT 2008 0 Wed Nov 26 11:00:00 GMT 2008 1 Wed Nov 26 11:05:00 GMT 2008 1 Wed Nov 26 11:10:00 GMT 2008 1 Wed Nov 26 11:15:00 GMT 2008 1 Wed Nov 26 11:20:00 GMT 2008 1 Essentially, I need a way of differentiating between indivi...

Item Transaction Entry
We currently have two companies and would like to know the best way to transfer inventory from one to the other. Currently we use Transactions-->Inventory-->Item Transaction and enter a (-) Qty to get the item out of Compay A, and do the same (+) Qty in Company B to get the item into the Inventory of Company B. We follow up with a report (using a batch name to mark these transactions) to do Intracompany transactions to match the totals. Without purchasing a 3rd party software to handle advanced intercompany transactions, what is the best practice at this point. Thanks Ric...

#N/B errro with vertical lookup
Hi, I have this formula: =VERT.LOOKUP(B2;\$J\$2:\$K\$8;2) J2 through K8 is: 2 maandag 3 dinsdag 4 woensdag 5 donderdag 6 vrijdag 7 zaterdag 1 zondag Everything works just fine except when the value of B is 1, then I get #N/B in the corresponding cell Waht to do? Thanks Try this: =VERT.LOOKUP(B2;\$J\$2:\$K\$8;2;0) I'm "guessing" that VERT.LOOKUP is the equivalent of VLOOKUP in ENGLISH. If so, then the arguments should be the same. Biff "Jean-Paul De Winter" <jp.dewinter@pandora.be> wrote in message news:Z3x9h.201122\$wA.2778204@phobos.telenet-ops.be... > Hi, ...

non vba way to print non continuous ranges #2
Thanks Myrna, I didn't know about the hide columns facility. It makes what I want to do a lot easier : -- DavidObei ----------------------------------------------------------------------- DavidObeid's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=223 View this thread: http://www.excelforum.com/showthread.php?threadid=27196 ...

converting checkboxes to a text value
I have an existing database that employs checkboxes. I'd like to create a report that displays those boxes as "yes/no" text values, so that I can export them to an .rtf format, and have them actually show up. I'm a novice, so any help is greatly appreciated. The purpose of this is to be able to email specific pages of the report. I'm also open to other ideas for making this possible, if I'm going about this the wrong way. Thanks in advance. Charitycase wrote: > I have an existing database that employs checkboxes. I'd like to > create a report that...

combining duplicate row values?
Excel 2007 XP Pro SP3 I have several rows of data as: 1 gl 24 36 cw1 north 1st 1 g2 24 36 cw2 north 1st 1 gl 24 36 cw1 north 1st 1 g2 24 36 cw2 north 1st 1 gl 24 36 cw1 north 1st that I would like to combine (preferably into a separate worksheet) as: 3 gl 24 36 cw1 north 1st 2 g2 24 36 cw2 north 1st based on rows 1, 3, and 5 and rows 2 and 4 having duplicate data. Is there a way to do this? Dave DDP 1) Use the options in advanced filter to copy unique records to a location in the same worksheet. (Advanced filter does not copy to alternate sheet). 2) Move the records to the required sh...

Can I sort by Range Name?
I have a complicated spreadsheet that contains what I'll call "su heads" and I need to sort by these. For example, I have this sort o data: Owner Name Pets Pet Age ------------------------------------- Randy's Pets Dog 3 Cat 5 Tim's Pets Cat 4 Bird 9 Sue's Pets Goldfish 1 How could I sort by the Owner Name, and retain the informatio organization under each name? Currently, all I get it is this result: Owner Name Pets Pet Age ------------------------------------- Randy&#...

updating a reg_binary registry value
I can update a string in the registry. But Im in a situation where I have to take a string, convert it to a byte[], and update a reg_binary in the registry, and so far my research and testing have been pretty frustrating. What is the simplest/most effect way to convert the string to a byte, and can I just use the SetValue to make the update, like I do with the string registry data type? Any help would be appreciated!. ...

Go to certain sheets based on cell value
Hello, I have created a drop down list with the numbers 1,2,3. If the user chooses 1 and presses enter, he/she is taken to 'AOA Only Analysis' sheet, and so on for the other two. Any and all help on this is greatly appreciated. Tushar Tushar, Instead of a drop-down, why not put your three entries in three cells? They can have anything in them, such as "AOA Only Analysis." Now set up hyperlinks (right-click the cell, and select "hyperlink") and set up the hyperlink with "Place in this document." -- Earl Kiosterud mvpearl omitthisword at verizo...

How to update values in message boxes from within VBA?
I have a macro (in VB) as shown down below invoked by F11 - I created it initially with record macro. It allows me to type in a name for an invoice and increment the invoice number. When I first press F11 it invokes two consecutive message boxes to accept input to update two fields on the invoice. The first message box comes up with the current value of the field from the invoice template (Client Name). I change the name in the this first message box and press OK on the message box. The second message box comes up with the current value of the field from the invoice templat...

Asking Excel Solver to use binaries, but selects other values
I am trying to get a linear program solved using solver. The values that Solver needs to enter are contrained as binary, but excel still try to find values that are neither 0 or 1. Stephane, I assume that when you say it is selecting values that are neither 0 or 1, they are values that are numerically close to 0 or 1 (such as 0.99998). If this is the case, you may want to consider the following approach: 1. Have the Solver constrain your decision variables as binary. 2. Have another set of cells which uses the decision variable to produce a result. When the underlying decision variabl...

The instance name must be the same as computer name.
I keep getting this error when trying to install MS CRM 3.0 and I cannot get around it! I only have a default instance of a SQL server and it is registered as the same name as the server?? Please help very frustrating Hi Damien! "Damien" wrote >I keep getting this error when trying to install MS CRM 3.0 and I cannot >get > around it! I only have a default instance of a SQL server and it is > registered as the same name as the server?? CRM must be installed on an SQL Server that has been installed as a default instance. So if the computer name is SCHILLER the SQL-S...

Cell Value vs Status Box Value
The cell value is not the same as the vlaue in the status box, e.g., status box shows 6147; cell value shows 64147 (format was with commas). Any suggestions/ideas? --- Message posted from http://www.ExcelForum.com/ awarner1 <<awarner1.11hdg3@excelforum-nospam.com>> wrote in news:awarner1.11hdg3@excelforum-nospam.com: > The cell value is not the same as the vlaue in the status box, e.g., > status box shows 6147; cell value shows 64147 (format was with commas). > Any suggestions/ideas? What is the number format of the cell? -- My email address has an extra @ (spell ...

Subtotal of values in cells containing formula
Hello! for eg. I have column A with each cells displayed of a value, by is actually generated by formula. How can I use SUBTOTAL in this case to obtain the subtotal of value when I use my filter. To clarify, my cell does not contain whole values like 2,500. It contains: (e.g) =(100*AP2)*'Exchange Rate'!\$V2\$13 My SUBTOTAL could not work in the above instance. Thanks to advise!! =) Hi, use =SUBTOTAL(9,B4:B6) change range to fit your needs, the above formula will bring the total despite you using a filter or not "KC" wrote: > Hello! &g...

Creating a variable range in Visual Basic
I've created a macro that will filter specific data from a sheet and copy that filtered data to another sheet for a "watchlist". There are multiple sheets to be filtered, and I'd like to be able to have the copied information paste in at the end of the previous data on the "watchlist" sheet. I'm not really good with Visual Basic, but can usually muddle my way through. Is there code I can use to make the range variable, so the info will paste where I need it to? Add this near the beginning of your existing macro. Dim lr As Long, sh2 As Worksh...