Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Add Prefix To Selected Cells


for a small online database I have a column that lists nationalities:

French
Spanish
American
Latin American
(etc etc)

I need to add a prefix to all nationalities, for example:

Artist Nationality///French
Artist Nationality///Spanish
Artist Nationality///American
Artist Nationality///Latin American
(etc etc)

Is there a way I can select the 700 cells and do this in one shot? I can't add formulas because I will have to paste all this in text pad and then upload it.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Prefix Cells With Text
I want to add "CR" to the beginning of every cell that already has data. I know I could do =a1 and it would copy all the way down. However each cell has different data and I'd like to keep it that way but add 2 letters in the beginning.

View Replies!   View Related
Replace One Prefix With Another Prefix
UDC 01_001 template black.jpgBatch 01_385 template white.jpg
UDC 01_001 template blue.jpgBatch 01_385 template blue.jpg
UDC 01_001 template grey.jpgBatch 01_385 template grey.jpg
UDC 01_001 template pink.jpgBatch 01_385 template pink.jpg
UDC 01_001 template white.jpgBatch 01_385 template black.jpg

I have 100s of urls with prefix as the left column. I need to rename them to the right column format. UDC 01_001 will take number Batch 01_385, UDC 01_002 will use Batch 01_386 and so on.

I cant figure out how to do this in one quick macro instead of replace function.

View Replies!   View Related
Add A Text Prefix
I have a colummn of data (text data). For my purposes I would need to add a prefix text to every single cell contained in that column; for instance, if I have a cell with a text "hellohowareyou" ,

I would need to change it by adding to it a constant prefixation text "es_ES@" so that the final result would be displayed like "es_ES@hellohowareyou" , and so on for the rest of the cells under the same column.

View Replies!   View Related
VBA - File Prefix Only?
Say I have a workbook called test1.xls, where the number 1 at the end of the file name is variable. Tomorrow that character could be 2 so the file would become test2.xls etc (or could even be a letter for what it matters).

Is there a way in Visual Basic to recognize that the final character is not fixed but can get different values each time? Or in other words to recognize that the file starts with "test", and that whatever follows that prefix is not relevant?

For example....

If ActiveWorkbook.name = "'test*.xls" Then...

where * (star) could be any character.

As a Windows analogy, I am thinking of the * (star) character when one searches a certain directory for, say, Excel files (*.xls etc). Obviously, in VBE that doesn't work, and the star is just a character like any other.

View Replies!   View Related
Trying To Use IF Statement For Prefix Only
Iím making a database to forklifts. Most used code I have found from this forum. I havenít found a way how to copy existing formulas on summary sheet so that sheet name is also changed in formula. I attached also my workbook, but itís not in English language but in Estonian. I hope that isnít problem.

If pressed button on summary sheet (ďPealehtĒ) then existing blank sheet are copied and renamed. On userform1 is textbox3 which value added to summary sheet, also combobox4 value added to summary sheet. Now I want that if pressed commandButton1 on userform1 then all formulas what exist on summary sheet from F6 to CW6 are also copied to next row and in formula sheet name changed. My second question is what to write that if new sheet created then it goes always to end of existing sheets. Right now Iím using code what copies new sheet after blank sheet but itís not satisfying me.

View Replies!   View Related
Removing 1- Prefix In Phone #'s
I read the thread below on how to utilize the Subsitute function to remove periods and thought about being able to use it for this. However, I have some phone #'s in my list that contain multiple 1- scenarios in them because the area code or 3-digit prefix sometimes include a 1- also. How do I make the formula only look at the 1- for long distance and not any other 1- found in the phone #? I want to remove all of the 1- for long distance because we are trying to use a new autodialer that is pre-programmed with the 1-.

View Replies!   View Related
Making Sure Cell Has 'J' Prefix
i have a cell "h23" that must always start with a "j" or a "J"
the trouble is some of my users are only putting in the number
ie 2345 when it should be j2345 or J2345

View Replies!   View Related
Filtering In Dropdowns With A Prefix
I'm not a novice but this might be something simple I didn't realize. I am creating a quoting sheet for a steel fab company. I have created dopdowns for the different materials and sizes but the dropdown is way too big. I need to have a column that helps me filter the dropdown more so I don't have to scroll through litterally hundreds of steels to find the one I need. The good news is that most steels have prefixes that make them easier to filter.

For example:

L-3x3x1/2
L-2x2-1/2x1/4

C6@8.2#
C8@11.5#

W12@16.0#
W21@44.0#

What I'm looking for is a way to have a dropdown that has all of the prefixes (L, C, MC, W, PL, etc) in it and when I select one of them the next column (the actual description of the steel as shown above) will only give me the specific steels for that steel shape (L,C,W,etc)

Currently I'm putting one row of say the MC shapes and one row of the C shapes and one row of the L shapes then copying them as needed. It's a lot of work and you can easily make a mistake.

View Replies!   View Related
Vlookup Based On Prefix
I have a table like below
A B
1 x1234 value
2 y1234 value

If I want to look for all values that start with "x", is there a way to do it within a vlookup formula? Something like: vlookup("x*", A1:B2, 2, False). I know there's other ways to do this, but I want it all contained in one formula instead of splitting the A column using LEFT(A1, 1).

View Replies!   View Related
Delete Worksheets With Specified Prefix
I have several excel workbooks with many worksheets (over 500 in some). Around a third of these worksheets are named "Exp1", "Exp2", "Exp3", etc.

I would like a macro that will delete all worksheets that do not have names starting with the letters "Exp". I do not want any confirmation dialogue, and as the workbooks vary in size, I would like it to finish when there are no sheets left (except the Exp ones of course).

View Replies!   View Related
Prefix Text On Entry
I have often used Data Validation List to create selection lists for cells in a worksheet. The problem is the list has to be on the same sheet you want to use it on. Is there any other way to do this so one common list can be used for all sheets? I have a list of accounts I want to use on 12 different Monthly tabs.

View Replies!   View Related
Extract Numbers And Prefix Letter
I need to extract (and then use for SumIfs) only item numbers from the long description. Please see the attached list where item number column shows existing list & next column shows what i want to extract. The exrtacted part if has any trailing or succeeding letters, characters between numbers should stay. for example from "SGA:RV-SVA:PEPPERS/PEPPERONCINI:SV9176001/232034" I need to extract " SV9176001/232034" or from " SPICES:BULK SPICES 7100:9054B" I need to extract " 7100:9054B". Can some one please urgently help me on this.

View Replies!   View Related
Adding Zeros To The Prefix Or Certain Numbers
Im basicaly pulling a report wherein the details will have a column as number.

It basicaly should be a three digit number. The tool will have the information as below.

VNO005016017008019020021

But when im pulling out the report into excel, the zeros in the prefix goes away and the report looks like the below

VNO516178192021

I just checked the tools previllages and it seems that we cannot do anyting in pulling the data as it is in the tool.

So wht im trying to do is to save the report in a folder where i have placed a validation sheet which will have references of the parent report.

So when i open the report the reference will pull up the data and in here i would like to add a validation which will add "0" in the prefix if VNO is a 2 digit number or "00" if the VNO is a one digit number.

View Replies!   View Related
Removing Prefix From Part Numbers
I have a huge column of data. This data has few prefixes that I need to remove. I have a list of possible prefixes. Some prefixes are 1,2,3 or 4 characters long. Could you please suggest best way of removing these prefixes (VBA if possible)?

Following are some of the examples of prefixes:
AB
GD
KR
BCD
FP-
TJ-
W

View Replies!   View Related
First Value From Right Side In Selected Cells
i try do function, which will be search/monitoring in selected cell (colored Ė yellow, orange, blue) and display (in green/results column) the last value in row colored/selected cell (first value from right side Ė results will be only for row).

View Replies!   View Related
Importing Selected Cells Into VBA
Can you help with how to import into vba just the data in the currently selected cells?

What I want to do is to manually select a range of rows (in a single column) and upon running the macro put the data from each cell into the uppermost cell with each seperated with a comma.

I can manage the combining of the data but don't now the selection is read into VBA.

View Replies!   View Related
Color Selected Cells
When you are on a spreadsheet and you use the arrow keys on the keyboard to move from one cell to another and you can see the cell you are on because of the lines, is there a way to have the color of those lines a different color than black? I would like to be able to arrow from one cell to another and have the lines be red or green so that I can see easier which cell I am actually on. Sometimes it can be hard to see which cell you are. Even if I could make those lines bolder to show up clearer which cell I am on.

View Replies!   View Related
Selected Cells Transport
I want to transport the other column that they not changed rows that I have been selected somerows at the column.

For ex.
at Column A
Rows 2,4,7,9,22,34 .all select cells are going to transport column B.But select rows are not going to change.

View Replies!   View Related
Count Selected Cells
How can I count the number of selected cells on an active worksheet? The number of rows and columns that a selected cell can be on change dynamically.

View Replies!   View Related
Hide All But Selected Cells
I want to be able to select a range on my spreadsheet, click a button, and have everything that is not selected hide itself. I've tried using intersect, but I'm not sure how to (quickly) loop through all of the columns and rows to see if my range is contained within.

View Replies!   View Related
Random 3 Letter Prefix Form (left, Right, Mid)
i need to create a random 3 letter prefix form eg. British Head Office, how would you pick letters out and compare the 3 letter prefix back to a column with existing prefix. and if u can help me even more how can i automate this method beacuase i for eg. get like 50 company names at a time and dont have time to do and check them 1 by 1 lol.


View Replies!   View Related
Sum Values With Names Based On A Number Prefix
How can I sum values with names based on a number prefix? For example, in the attached sheet, how can i sum all values that have the prefix 4.10.02.xxxx?

View Replies!   View Related
Naming Sheets With Time-dependent Prefix
I have got this macro working OK but now I need to add a 2 letter prefix/suffix depending on what time it was created (am or pm). Detail: If the sheet is created between 0600hrs and 1800hrs then "DS" and likewise between 1800hrs and 0600hrs then "NS". Outcome: The sheet will then have a name like - "22 Mar DS"...code included below

View Replies!   View Related
Pasting Into Previously Selected Cells
I have a set of four sheets that each act as a wall planner type of calendar (one for each team of staff) all in one workbook. I have a sheet that acts as a key where cells on the key display different types of holidays e.g. Annual Leave, Unpaid Leave etc. Normally the user would select the cells where they wish to take the time off against their name and go to the "key" sheet, copy relevant cell and go back to their team sheet and paste. I want to put all this into a macro to be used on a customised button on the menu bar.

I am able to do this with one sheet ( I simply put in the code the name of the sheet that I previously selected) but I don't know how with four sheets. How do I say go back to the sheet I previously selected.

View Replies!   View Related
Calculate The Sum Of Selected Cells From Another Workbook
I am trying to create a workbook that calcualtes the sum of selected cells from another workbook. I have quite a few formulas, so i would like to use the fill command to help. I cannot get excel to pick up the pattern so I can use the fill formula. The pattern is as follows.

Sum(n5:n11)
Sum(n12:n18)
Sum(n19:n25)
Sum(n26:n32)
etc.


View Replies!   View Related
Paste A Selected Range Of Cells
I think I have the comand to select a range of cells, but can not figure how to paste this selection later in the spreadsheet.

This is how the application works.

I have a spreadsheet that I am using as a template. The first 10 rows have to be repeated later in the same spreadsheet after I make a manual page break.

I the spreadsheet I am doing the following commands:
With xlApplication.ActiveSheet
xlPageBreakManual
.Rows(istartrow).Pagebreak = xlPageBreakManual
End With

View Replies!   View Related
Display Userform When Certain Cells Selected
I have created a Multipage Userform which I want to control the display when certain condition is met. I am using a button to call up this userform but I wanted to put some limitation to this form being displayed. This form will only be displayed when any of the cells in Column B Row 20 downwards or Column D Row 20 downwards are selected.

View Replies!   View Related
Copy Cells In Selected Row To New Sheet
I would like some code to allow the user to select any row in Sheet1
and then the populated cells (10 in every case) in that row will copy across to various locations in Sheet2.

eg,
The user selects Row header for Row 12 which has 10 cells ( A12 to J12) populated.
A12 will go to C3 on Sheet 2, B12 to D5, C12 to D9, etc, etc.


View Replies!   View Related
Inserting A Column To Thr Right Of Selected Cells
This may sound trivial, but how do you insert a column to the right of the current selection?

If I do Selection.EntireColumn.Insert that inserts a column to the left. Anyone know how to do this?

View Replies!   View Related
Averaging Selected Cells Excluding Zero
I wanted to average selected cells excluding the value zero. Also, I wanted to make the value of cell is zero and not to display the error #DIV/0! if the denominator is zero.

A1 = Denominator

Selected cell:
A3, A5, A6, A8

View Replies!   View Related
Show Gridlines Only In Selected Cells
Is it possible to show gridlines in some cells and hide gridlines in others on the same worksheet? Is it also possible to isolate a row or column and change the size of selected cells without changing the size of the cells in other rows and columns?

View Replies!   View Related
Set VBA Range As Cells Selected By Cursor
The VBA code (in the code window) runs nicely on the range B10:B1000, but I'd prefer that it only run on a range I define by the cells that are currently highlighted/selected on the active sheet. How should the line of
Set SHOPS = Range("B10:B1000")

View Replies!   View Related
Copy Cells To Right Of Selected Cell
I am using vlookup to get some data from one shhet in my workbook onto the front sheet
so I have a search box and the vlookup duly does as it should and gets me the data into E28:E100 and fills the cells to the right with corresponding data for each item
Now I select any cell from the first column of the search results I want and it gets pasted into the next empty cell range C4:C22 all this works a treat thanks to lots of help from mr excel. What I want to do (without any more vlookups) is pick up the corresponding cells to the right of the selected cell and paste them into the cells to the right of the pasted cell. I cant do it all at once because there is a different amount of columns between the select area and the paste area... hope this is clear
here is a snippet of code that lets me choose the cell to paste

Dim UserResponse as range
'InputBox to Ask the user to select the cell they want copying
'First direct them to the start cell in the column they are picking from
Range("E28").Select
On Error Resume Next
Set userResponse = Application.InputBox("Choose from List Below", Default:=Selection.Address, Type:=8)
On Error GoTo 0
If userResponse Is Nothing Then
End

Else
userResponse.Copy
(here I want to get the address of the cell into store so I can pick up the addresss of 2 cells to the right of it ie selected cell is E54 and I want to copy that and G54 and N24 and paste them into C8 D8 and F8 repectively)
End If

View Replies!   View Related
Merge The Contents Of Selected Cells
this is my first post in this great forum. let me start talking about what I need, I used to convert some statement from PDF to XLS format for my work purpose. sometimes I am facing a problem that some text in the statement will come in more than one row, actually I need it to come in one cell but it is coming in several rows that mean, if I want to sort or filter my data, I will lose some important data. What do I want???

I want a macro to merge the contents of selected cells and put it in the first cell of the selected cells then clear the other cells. I will use this macro for the following cases: Case 1- assume we have this data in any sheet.

B1=How
C1=are
D1=you? ( all without any space )

after selecting the said cells and applying this macro, the result will be:

B1=How are you? ( one space only between the content of the selected cells )
C1=D1=empty

Case 2- assume we have this data in any sheet.

B1=How.........

View Replies!   View Related
To Sum Visible Cells Only In A Selected Array
Is it possible to just sum visible cells only within a desired column or selected array?

I have visible cells in Column C10:C90,

View Replies!   View Related
Check Selected Cells For Information
I have the following code in a macro of mine which checks each cell has something entered and if not gives the message box.

View Replies!   View Related
Copy Selected Cells From One Sheet To Other
i need to copy the selected cells, and paste them on another sheet on same locations, this should be done on button click

View Replies!   View Related
Prefix Negative Sign To A Positive Numeric Character
The report generated from our co. software in excel have negative figures suffixed as "-" for ex., payable shown as 21373533-

- The value is treated as text in excel

- How should i Bring the negative sign for this type of data as prefix
when there is huge data and also in a columnar manner.

View Replies!   View Related
Repeat Insert Row And Formula For Selected Cells
I am in the process of developing a budget template for next year and here is what I want to accomplish through Macro to avoid the tedious mannual manipulation process (hope that's achievable). BTW, I am only an entry-level Macro user who has no background in coding.

I have attached an Excel sample but let me explain:

In each of the expenses tabs (1, 2, and so on (only 1, 2 given in the sample)), I want to be able to select a range of the data cells (A19:Q34 in "Expense 1" and A10:Q28 in "Expense 2") and run this Macro so that:

1. 10 blank rows be inserted between selected data rows

2. A sum created for each month from the four rows below) with different color

3. A Year to Date Actual and To Year End Forecast created based on the VLOOKUP of the "P&L" matching the account code by month

4. Finally grouping of the first 9 rows.

the above is what I have manually created for the first account code in the "expense 1" tab. I have 7-8 expenses groups and about 200 account codes. So mannually creating the above for each expense groups is not that terrible if this will make life easier for Macro.

I would think this is a very typical Macro issue but can't really come up with anything.

The other issue I ran into the VLOOKUP for the YTD Actual and TYE Forecast. It is to look up accont code in the "P&L" tab and match the #s. But the column numbers in the VLOOKUP do not change automatically when I copy across. Thus I have to change mannually, which is really a pain in the butt. I also know I can't copy down because that will change the account code that I want it to match. Because to be able to copy the VLOOKUP formula across the same account code, I need to use the $ to fix the account code. But to copy down I think I need that to be without the $ sign. Any solution on that?

View Replies!   View Related
Macro Or Function That Changes Value Of Selected Ranges Of Cells
I am looking for a macro or function (VBA) that will modifiy the value of the selected cell or cells. the code should support selection of one cell, a range or multiple ranges.

I envision the user making his range selection(s). Activating a function or clicking a button that would pop open a modal window. The user would have the option to either adjust the values by a % change (i.e. up or down 7%), or incremental change (i.e. up olr down 100 units). The function would overite the value in the cells.

Has anyone ever done something similar? Is it hard to program?

View Replies!   View Related
How Do I Total Selected Data From A Range Of Cells?
On worksheet 1 I have Column A with a list of names, which we shall call John, Paul, George and Ringo, listed randomly within 100 cells (A1 - A100).
I have Cells B1 - J100 with 3 options in each cell; Blank, C and NYC.

I need a list on worksheet 2 for each individual person showing;

(1) total number of cells marked C
(2) total number of cells marked C & NYC combined.

View Replies!   View Related
Loop Down Rows On Multiple Selected Cells
I have a sub that will join the text together from multiple columns into one cell but it only works for one row. I would like it to work through the rows selected. How do I do this?

Sub JoinText()

myCol = Selection.Columns.Count
For i = 1 To myCol
ActiveCell = ActiveCell.Offset(0, 0) & ActiveCell.Offset(0, i)
ActiveCell.Offset(0, i) = ""
Next i

End Sub

View Replies!   View Related
Macro Leaves All The Cells In The Range Selected
I have a worksheet with ever expanding data - rows at the bottom of the data
are continually added. I have a simple macro that sorts all of the data
according to preset parameters and selects the next blank cell in column A,
ready for more data:

Sub Macro5()
Range("SortRange").Select
Selection.Sort Key1:=Range("SortRange"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom
Do Until ActiveCell.Value = IsEmpty(True)
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

When running the Macro, this leaves all of the cells in the range 'selected'
(ie; coloured-over). What do I need to add to the Macro to just select the
cell in Column A and remove the highlighting from all the other cells?


View Replies!   View Related
Concatenate Variable (user Selected) Cells
I want to do a concatenate for multiple fields, but want the user to specify which cells should be concatenated by entering this in a few fields. What does this formula (or VBA) look like?

Let's assume there is the possibility to concatenate a maximum of 4 cells. Call the 3 cells that will be concatenated cell1, cell2, cell3 and cell4.

On the first sheet of the workbook the user enters the following:
cell1 = Sheet2!A4
cell2 = Sheet2!B4
cell3 = Sheet2!C4
cell4 = Sheet2!D4

Here are some cells and their values:
Sheet2!A4 = Hel
Sheet2!B4 = lo
Sheet2!C4 = World
Sheet2!D4 =

Upon enterring the information after the = sign for the cell1, cell2 and cell3, the formula for the concatenate becomes: =CONCATENATE(Sheet2!A4, Sheet2!B4,Sheet2!C4 ).

The result of the formula is: HelloWorld

If I now change the values of cell1, cell2, cell3, and cell4 the following should happen:
cell1 = Sheet2!B4
cell2 = Sheet2!C4
cell3 =
cell4 =

The formula for the concatenate becomes: =CONCATENATE(Sheet2!B4, Sheet2!C4).

The result of the formula is: loWorld

View Replies!   View Related
Handling Error If 2 Cells Are Accidentally Selected
what i could do to the code below so that if 2 cells are accidentally highlighted - it wont come up with a type mismatch error on the line below in red.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With ThisWorkbook.Sheets("price").Range(ActiveCell.Address)
If .Value = "" Then
UserForm2.TextBox1.Value = "No Trade Selected"
Else
UserForm2.TextBox1.Value = Format$(WorksheetFunction.RoundUp(Val(Sheets("price").Range(Target.Address).Value), 2), "$ 0.00")

End If
End With

End Sub

View Replies!   View Related
Copy Selected Cells/rows To Another Worksheet
I have set up the attached workbook to try to show the results that I need using the matching shaded cells in the worksheets.

The first part of the problem is that all data in columns A, B, C & H (Input1) needs to be copied to columns A, B, C & F in Input2 with the addition of a Zero value in columns D & E (sample data lines 1 - 3).

The second part of the problem is that all data in columns A, B, & F (Input2) where the cell in column C is equal to ZERO needs to be copied to columns A, B & C in Input 3 (sample data Lines 4 - 11).

It would also be useful to be able to copy data in columns A, B, & F (Input2) where the cell in column C is NOT equal to ZERO to columns A, B & C in Input 3 (sample data Lines 12 - 25). However, there will be often be more than value in column C (Input2) dictated by the date in Column A and number in Column B. The value in Column F (Input2) will always be the same for each of those rows, so it does not need to be duplicated in Column C (Input3).

This would have to be manually triggered by linking it to a button or menu item, not sure what would be the best option. As data is entered at various times of the week, the routine would either have to overwrite existing matching data or carry on from where it last finished!

View Replies!   View Related
Apply Formula To Selected Cells Via Macro
i m trying to set up a macro to convert a range of user-highlighted(selected) cells to 3 significant figures: for example, convert 0.135564 to 0.136

the equation i found elsewhere online: ROUND(xx,3-(1+INT(LOG10(ABS(xx))))). but i can't quite figue out how to apply the equation to a selected range of cells via a macro.

View Replies!   View Related
Cursor Movement :: Move To Selected Cells Only
to make cursor to move only on the selected cells.

E7, O21, O22, O23, O24, Z23, Z24, Z26, Z27, Z28, C26, C27, C28 ...

View Replies!   View Related
Highlight Cells Matching Selected Cell
I would like to select a cell inside my workbook and when it is selected it automatically highlights similar value cells in the same workbook. Is that possible? A Look at the attachment should give you a much better idea as to what I am talking about.

View Replies!   View Related
Take Action Based On String Value In Selected Cells
I am trying to take some action based on the string values in selected cells. Here is my code to iterate through selected cells and count the occurances of certain string values:

Private Sub TakeAction()
Dim cel As Range
Dim sel As Range
Dim complete As Integer 'a counter for completed
Dim pending As Integer ' a counter for pending
Dim cancelled As Integer ' a counter for cancelled
Set sel = selection
For Each cel In sel
If cel.Text = "Some text" Then
complete = Complete +1
If cel. Text = "Some other text" Then
pending = pending +1
If cel.Test = "Yet some other text"
cancelled = cancelled +1
End If
End If
End If
Next cel

MsgBox complete
MsgBox pending
MsgBox cancelled
End Sub

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved