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


Advertisements:










Adding Character To End Of Each Row


A list of URL's in each row. Here's an example:

webmd.com/diet/rapid-weight-loss
webmd.com/food-recipes/features/10-everyday-super-foods?page=2
webmd.com/news/20000529/over-the-counter-drug-works-to-suppress-appetite
weightdepot.com/
weightloss.about.com/
weightloss.about.com/od/easyweightlosstips/
weightloss.about.com/od/southbeachdiet/a/aa060705a.htm
weightloss.com.au/

Here's what I'd like to accomplish: For each row that contains a URL that ends with a / I would like to remove it. If I use the find/replace feature, it would remove all /'s, which I don't want since a good majority of the URL's contain the /'s in various parts of the URL, such as in the example above. I'd also like to accomplish adding a / to the end of every URL for a list that does not have the /'s at the end.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Adding An Updated/revised Date/time On The End Of Row.
I am trying to record the date with time on a pre set cell of a row, in which the cell will show the most current updated date/time once any change is made within the same row, regardless which cell on the row. Is there any code for VBA you can suggest?

View Replies!   View Related
Adding A Character To A Cell
I have a number in each cell 42189654 and I want to add a period in the 3 place. Example 42.189654. I cannot seem to get the correct formula.

View Replies!   View Related
Adding A Space Where The Cleaned Character Used To Be
I was able to get rid of the weird character [] with the =CLEAN(E11), where E11 had the problem character. but now where the [] used to be between to words the 2 words just show as 1. Example If the bad cell was just[]test the clean cell has justtest without a space between the 2 words. Since the spreadsheet is about 8,000 cells and has random [] characters is it possible to not just clean the bad characters, but add a space where they used to be all in one command?

View Replies!   View Related
Adding A 1 At The End
I have a 9 digit number in excel. I'd like to write a formula that adds the number 1 to the end of each number.


View Replies!   View Related
Adding Commas To The End Of A Word
1. In a single column I have a list of random words e.g in column A, I have 1000 words going vertically down, in columns 1 down to a 1000. I would like to know how I can add a comma to the end of each word automatically?

2. In column A I have a list of 2,609 words going vertically down (the amount of words could be any random amount - even and odd numbers). In column A for the first 20 words I would like to automatically put the words "GROUP 001" in Column B next to the first 20 words in column A, thus the words "GROUP 001" would be repeated 20 times in column B.

For the next 20 words in line in Column A, I would like to put the words "GROUP 002" in Column B, thus the words "GROUP 002" would be repeated 20 times in column B. In this example there are 2,609 words so 2,609 divided by 20 = 130.45, so there would be GROUP 001 to GROUP 131 in column B.

View Replies!   View Related
Adding Zeros To The End Of Numbers
I have a large spreadsheet of employee data saved in a shared network folder so that others can access it. Starting about a week ago, anytime I enter a number in any cell, Excel adds two zeros to the end of the number. For instance, a 1 becomes 100.

When other users open the file on their computer and enter a number, everything is normal - no zeros are added. I'm guessing I inadvertantly enabled a function somewhere along the line, but have been unable to figure out how to undo the function.

View Replies!   View Related
Adding Text To End Of All Cells In Column
I have about 5000 rows of data and I need to add the same letters (.jpg) the the end of all the data in a column.

View Replies!   View Related
Adding Values In Cells (stops Adding After Row 14)
I am trying to add numbers from cells if it IsNumeric and for some reason in column K the macro doesnt recognize numbers after row 14?

The range column is "E4:E"
Search criteria is the letter "R" in column "E" Then using OffSet, I go thru other columns and process data. Most of the macro works except for column K after row 14?

View Replies!   View Related
End(xlUp).Row Not Finding Last Row/Cell
After searching the forum, I thought I'd found the solution to pasting in the next empty row. I have a macro in one workbook (well, there's 17 of them!) that selects a specific sheet's UsedRange - less the heading row - and copies it (this works). I then switch to the master workbook and click another button to paste the data; the macro finds the correct sheet and pastes the data (1000 records) but when I paste data from the next workbook, it starts at A1 instead of ws. Range("A" & (LastRowA + 1)).

Sub PasteRCdata()
Dim ws As Worksheet
Dim LastRowRec As Long
Set ws = ActiveWorkbook.Sheets("data1")
LastRowRec = ws.Range("A65536").End(xlUp).Row
On Error Resume Next
ws.Range("A" & (LastRowA + 1)).PasteSpecial xlValues
LastRowRec = 0
Application.CutCopyMode = False
End Sub
Here's the code that copies

Dim rng As Range
'code here to select the correct sheet
Set rng = ActiveSheet.UsedRange
rng.Offset(1, 0).Resize(rng.Rows.Count - 1, _
rng.Columns.Count).Copy

View Replies!   View Related
Macro To Copy Row To End For Row 1
I am trying to find a macro that will copy row 2 and place this data in the second open cell in row one, then copy row 3 and place in the second open cell in row one. This would go down about 500 rows and out to IV. Of course when I get to IV the data would then be placed in row two and so on.

For example

Columns
rows A B C D
1 Cat

2 Bat

3 Hat

4 Sat


Columns
rows A B C D E F
1 Cat Bat Hat Sat

2

3

4


View Replies!   View Related
Adding Data By Row Based On Typing In Cell Contained In Row
I've built (but not completed) a spreadsheet used to organize multiple Bill of Material lists. This list is a row-by row list of products with information relating to a specific part number including: Mfg, Part#, Qty Used, Cost Ea, Vendor, among many more.

Here's what I'm trying to accomplish first. As this list grows, my intension is the be able to recall a row of data (or control what columns of the row are recalled) from this list and be able to easily add them to the list. I would like to be able to do this by typing in data in one cell in the row (like the Part#), and then have a macro or VB code that will automatically fill in a defined number of columns with the descriptive data like: Mfg, Cost Ea, Vendor, etc.

Currently, the data for the rows (if it already exists) would reside higher up in the list on the same worksheet but could also potentially be on another tab in the workbook.

View Replies!   View Related
Erasing Set Of Character In A Row
I'm looking to create a macro that will take a input from extract master.xls workbook and search that contain in the transaction.xls workbook.

I need not want to search the entire row exactly, instead if the search string found any where in that row, that search string alone need to be removed from that row.

For example.

Search String (File : extract master.xls, Column : TAG) :- ....

View Replies!   View Related
First Character In Cell Is Text Then Delete Row
In a column I have cells where the first character is numeric and the rest text and also cells where the first character is text.

I want to delete the rows where the first character is text.

View Replies!   View Related
Delete Row Based On Starting Character 1st Then Contains
I would like to create a macro that deletes all rows starting with an open parenthensis in column B. The contents of what is in parenthis varies from 2 letters up - so anything starting with a parenthis is sufficient criteria.

After the initial deleting (mentioned above), would also like to delete (from column B again), rows containing specific, multiple phrases.

None of the functions I have see so far will facilitate this...


View Replies!   View Related
Transfer X Row To The End
in my worksheet if i have in any cell of clomun k eg.rnage (k1:k50) the word "fail" i want to transfer all of the data in this row to the fist empty row =to the end of the used rows


View Replies!   View Related
Find Specific Character - Copy Into Next Row & Continue
I have an excel file in which the cell has more than 3000 entries, like following. I would like to copy the contents before ";" to next row & continue.

Question:

CN=123,CN=12,CN=ABC,CN=AB,;CN=345,CN=34,CN=CDF,CN=DC,;CN=510,CN=51,CN=PQR,CN=PQ;

Answer should be in excel:
CN=123,CN=12,CN=ABC,CN=AB
CN=345,CN=34,CN=CDF,CN=DC
CN=510,CN=51,CN=PQR,CN=PQ

I do not know how can I achieve this through "Macro". I would like to detect ; in the cell & copy the contents before/after it in the next rows & continue till the last figure.

View Replies!   View Related
Move To End Of Used Range In Row
Is there a key combination to move Right to the end of the row even though there may be blanks within that row?

If I do a Shift>Ctrl>Right arrow it will highlight to the cell just before the first blank cell.

I am trying to "Move" to the Right end of the table reguardless of what cell I'm in or if there are blanks in the row.

View Replies!   View Related
End(xlUp) Leave Last Row
the below code adds a new row with data to the last blank row on the sheet how do I change it so that it leave the last row and inserts above it.

the last row is a sum of the columns ....

View Replies!   View Related
End(XlUp) For Last Used Row In Column
I have copied a piece of code and am trying to understand how it work. The scenario is that the following values are in Column A (starting with cell A1) i.e. A1 contains the value 81 and A5 contains the value 124:

81
44
713
997
124

I can't understanding the working of the following line of

iRow = Worksheets("Sheet1").Cells(Rows.Count,1).End(XlUp).Row

Here's my understanding:
The focus is moved to the last row of Column #1 in Sheet1. This is achieved by
".Cells(Rows.Count,1)" Then the focus moves up to the first cell that contains any value. This is achieved by
".End(XlUp).Row"

I was expecting that after execution of the above code, iRow it will hold the value 124 (which are the contents of the 1st row that is encountered when the code executes ".End(XlUp).Row"). However the debug window shows that iRow contains the value 5? Seems like that contrary to my understanding, iRow is holding a pointer to A5?

View Replies!   View Related
Add Sum To End Of Row
i need a macro code for this. example

this is the Original Workbook, columns D and E are hidden
A B C F
1 1 1 1
2 2 2 2
3 3 3 3
. . . .
. . . .
nth

there's a command button here that when clicked, it will open a new workbook
showing like below

example
this is the new workbook
A B C D
1 1 1 1
3 3 3 3
. . . .
. . . .
nth

there is no problem on that part, I already have the code for that. I only need the code on 1. how to compute the sum of column C then placed the total on end row of column C. 2. how to compute the sum of column D then placed the total on end row of column D. how to determine if it is the end of the row on Column C, if it is the last row then compute the total, and then do the same on D.

View Replies!   View Related
Detect End Of Data In Row And Move To Next
if i have data across multiple rows than is it possible to analyze that data in say row 1 and once row 1 ends move to row 2. example

Array Data

Colmn A B C D E
Row1: 3 1 4 6 8
Row2: 6 7 8 9 10
Row3: 4 5 1 2 8
Row4: 6 4 3 2 1
Row5: 2 4 1 2 2

OutPut Format

Colmn F
Row 1: Age is 3
Row 2: Always Same - Note this appears even though not part of data
Row 3: Year is 1
Row 4: Time is 4
Row 5: Hour is 6
Row 6: Minute is 8
Row 7: !
!Not Part of Output Comment: Now row 1 data has ended. Move to Row 2!
Row 8: Age is 6
Row 9: Always Same - Note this appears even though not part of data
Row 10: Year is 7
Row 11: Time is 8
Row 12: Hour is 9
Row 13: Minute is 10
!
etc
etc
etc

also note that the text that comes up in each row is not part of the data portion. Its just the number or string that gets pulled from the data portion.

View Replies!   View Related
Eliminate Rows (loop To The End Of Each Row)
I'm trying to write a macro that will loop to the end of each row, if the last cell is a number it will cut and paste that whole row into another sheet, if the last cell is a word then it will leave it as it is.

View Replies!   View Related
Deleting Columns A,B,C, And Row 3 To The End Of Sheet
I am trying to build a macro to delete the cell content for the entire A column, B column, C column. Then i would like the macro to delete the cell content from row 3 to the bottom of the sheet.

View Replies!   View Related
Insert A Row At End Of Set Criteria
I have a spreadsheet detailing account numbers (there are more than 1 of the same account number) that I currently filter by text (contains) and an additional filter to sort from A to Z

What I would like for my next step (I am looking to create this as a macro) is insert a row when one account number stops and another starts

eg:
A/C
SS501
SS501
SS501
SS503
SS503
SS503

I would like it to read

SS501
SS501
SS501

SS503
SS503
SS503
etc.. Without having to insert row manually.

View Replies!   View Related
Add Row To End Of Table Range
I have 2 lists seperated by a blank row For example:

Title1:
Acct # Acct Type
aaaa bbbb
aaaa bbbb
aaaa bbbb

Title2:
Acct # Acct Type
aaaa bbbb
aaaa bbbb
aaaa bbbb

How do I create a macro (for each list) to insert a new row at the end of its respective list?

View Replies!   View Related
Deleting Content Of A Cell Till The End Of Right (Not Row)
I need help on Excel 2007 Macro , the below coding searches for a string "recvtiming" in a cell as a wildcard and delete it entire row once it founds it in a single active sheet.

Question,

1. How do I manipulate it to search for the same string and upon finding

a) Delete its cell content and all the contents from its right hand of side till its last column which have a value from the same row on all 50 sheets in the workbook.

e.g
a b c d recv 0 1 2 3 4

It should return,
a b c d

View Replies!   View Related
Automatically Enter To Next Row Once The Typing Has Reach The End
Is there a way that Excel can automatically enter to next row once the typing has reach the end.

Example: I have 5 columns (A to E) and I am typing at column A. While typing, the text will go along to cell B, C, D and E. The problem is, if I don't manually go to next row and continue typing, the text will go to column F, G and so on. Is there a way where excel can automatically jump to next row if the text has reach column E?

Another problem is, say I have 3 rows full of text (column A to E). If I edit one of the row to exter some new texts, the whole sentence will go along to column F, G and so on. What I can do now is, re-edit all the rows to adjust them back.


View Replies!   View Related
Define Range, Given Cell Address To End Row
I want to define the range from a given cell and all data below within the same column strDataStart is the named cell in the worksheet that want to start from. Below is what I tried and failed.

Function rngDataCol(strDataStart As String) As Range
Dim rngDataEnd As Range
rngDataEnd = Range("strDataStart").End(xlDown)
rngDataCol = Range(Range("strDataStart").Address, rngDataEnd.Address)
End Function

View Replies!   View Related
Row Will Be Inserted Above The Row With The Word "end"
when you run the macro AND when the word "end" is in any cell in column A then the following two things will automatically occur.
1. A row will be inserted above the row with the word "end", and
2. The formula in the "above row" in column B (right cell and above to cell with "End" word in it) will be dragged down into the new column.

What I want are the formulas in all the cells in the "above row" to be dragged down into the new row - not just column B.

Option Explicit
Sub InsertRowAtEndAndCopyColumnBFormulaDown()

Dim lInsertRow As Long

lInsertRow = Columns("A:A").Find(What:="end", After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
Rows(lInsertRow).Insert Shift:=xlDown
Range("B" & lInsertRow - 1).Select
Selection.AutoFill Destination:=Range("B" & lInsertRow - 1 & ":B" & lInsertRow), Type:=xlFillDefault
Range("B" & lInsertRow).Select

End Sub

View Replies!   View Related
Looping Macro (copy And Paste It To The End Of Row, And Make It Value)
I've tried to record macro, placed formula in row 4 only, copy and paste it to the end of row, and make it value. My database begin form column AM to BG.

Sub UPDATE_DATABASE()
'
' UPDATE_DATABASE Macro
' Macro recorded 2/22/2010 by FAUZAN
'
Sheets("database").Select
Range("AM4").Select
Selection.Copy
Range("AM5").Select.........

View Replies!   View Related
Macro To Copy Cells Beginning From A Certain Row To The End Of The Sheet
I guess this is a simple one but I am not sure of the best way to do it,
I have number of points recorded for everyday in an excel sheet where first column represents date, second column represents group third column represents line and fourth column represents point as shown in the example below

3 19 2222 4324

2223 4325

2224 4326

2225 4327

2226 4328

"3" and "4" represent the 3rd and 4th day of the month, "19" represents the group and 3rd and 4th columns rest are line and point numbers recorded (2222 is first line of day 3, group 19 and 4324 is first point of line 2222 of group 19 on 3rd day and so on....).

I need a macro (or formula) to copy the cells of the last day written in the sheet up to the last row to a new excel sheet, reformat them so that first column has the word "Date" in its first cell, second column has the word "group" third has "line" and fourth has point.

so in the example mentioned here I need it to copy the cells of day "4" including the group, line and point numbers and format them in the new sheet so that first column has "DATE" then 4 for the rest of the rows, second column has "group" and 19 in the rest of the rows, 3rd column has "Line" and all line copied up to last row, 4th column contains "point" and all points copied.

View Replies!   View Related
Add Character After 1st Character Of Cells
Need a formula/code that will determine what the corrected part number should be (insert dashes if they are missing) by comparing to other values in the list.

Original A1:A5 = {452, a-bc, 123, 4-52, abc}
Corrected C1:C5 = {4-52, a-bc, 123, 4-52, a-bc}

I can do this using an intermediate working column, but can this be done all in one formula, or via VBA?

My formulaic solution is thus: .....

View Replies!   View Related
Append All Digits Before Character To All Numbers Between The Same Character
I have got a list of numeric abbreviations, for instance 10739011/21/31/41. What it should really display are the numbers 10739011, 10739021, 10739031 and 10739041 (the first six figures stay the same). All the numbers in my list are 8 figures long. I want to change the list from the list seperated by the backward slash to the complete numbers. I have uploaded an example of the list with backward slash between the numbers. Is there a way that Excel can automatically change these numbers to the full numbers?

Because all the numbers are 8 figures long, I thought the first 6 figures of the 1st number can be copied and those 6 figures pasted before the other two figures after the backslash. Auto Merged Post Until 24 Hrs Passes;sorry, pressed OK too quickly. The problem is that there are sometimes 4 numbers in the cell, sometimes 6 and once three. I would like Excel to complete all the numbers in the cell and then move on to the cell underneath it and so on. Also, I would like each number to have it's own cell.

View Replies!   View Related
Arrays And IF(OR( -comparisons With A Positive/negative Or Yes/no Error Displayed At The End Of Each Row
Looking for any assistance: Excel 2000 (9.0.6923 SP-3) Windows XP Pro SP-2
I am trying to get Excel to sort through data in various cells,(text and numerical) and do comparisons with a positive/negative or yes/no error displayed at the end of each row.Suggestions so far range from don't know to use Arrays, I am swamped in MSN help pages and am completely lost now. here goes:

In the example below I want Excel to compare Column P4 against Column M4 and display a result as above, so for instance British Midland (M4) will always be 40100350(P4) & Heathrow Express will always be 40100355, British Rail (Tickets) will always be 40100355,so I want Excel to compare all Suppliers against their corresponding codes for each row Eg (P4 to compare to M4) and display if this is correct or not, the codes 40100350,355 etc do not change ....

View Replies!   View Related
Adding Every Other Row
I want to add Columns B through R, starting on row 4, add row 4, then 6, then 8, then 10 and so on, till about 30. Does anyone know the formula?

View Replies!   View Related
Adding Value In The Last Row ...
How to add values in the last row under the latest data that already existed ... is a loop required in the problem?

View Replies!   View Related
Find The End Value In A Row (right Side) And Select The Empty Cell To Its Right To Paste Data In
i need a method to find the end value in a row (right side) and select the empty cell to its right to paste data in.

problem is the row where this data is may change so using

limit = (row, col).end(XlRight).Col


View Replies!   View Related
Adding A New Row Automatically
I have a work book of 3 sheets . the date in the first sheet are linked with the other 2 sheets , sometimes I have to add a row in the first sheet , within the the the table it contains ,

what I need is : a new raw is being inserted (created) in the other 2 sheets with tha same date I entered in the new row I added in the first sheet . beside the possibility to let me difine where that new row is being inserted in each of those other 2 sheets ( separately).

View Replies!   View Related
Adding 2 Rows After Every One Row
I need some help fomulating a VSB code which would add two rows after every one row.

View Replies!   View Related
Macro: Copy And Adding New Row
I'm using this Macro to add a new row and it is adding the row just fine, but I would like only the content added in row B, C, D, and the rest of the rows left blank. It also will copy and add all rows right except for row 4 wich is the burnsville row. I have the macro attached to the shape in B 1and 2.

Sub InsRow()
Dim iRow As Long
iRow = InputBox("Enter row number")
Rows(iRow).Insert
Rows(iRow - 1).Copy
With Range("A" & iRow)
.PasteSpecial Paste:=xlPasteFormulas
.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End With
End Sub

View Replies!   View Related
Adding Two Rows After Every Existing Row
Is there any way to automatically add two new rows after every existing row containing data?

For example

1 A B C
2 D E F
3 G H I

turn into

1 A B C
(empty row)
(empty row)
2 D E F
(empty row)
(empty row)
3 G H I

View Replies!   View Related
Adding Times In A Row Of Cells
I am trying to add a ROW of times. Each cell of times is using the 00":"00 format so I don't have to type a colon between the hours:minutes.

I'm not sure of what the formula should be for the Total Hours box.

For example, if I were to type 13:45 in the start time & 14:00 in the end time, I am getting a result of 0:55 which is incorrect

Another example if I leave the first 2 start/end boxes empty(blank) and then type 15:30 Start & 17:00 in the second set of Start/end boxes, I get a Total Hours of 1:70 which is also incorrect.

The formula I have in the Total Hours box is =SUM(B12-A12)+(D12-C12) which is know is incorrect.

What is the correct formula which will give me a Total Hours which adds a row of times correctly?

StartEndStartEndTotal Hours

View Replies!   View Related
ADDING A ROW Till Certain Column Only
Can I have a macro code to insert a row from column A:K.

I have data in column L to IV which I want to stay as it is. i.e. for example when a row is inserted from A:K, that row should not extend beyond Column K.

View Replies!   View Related
Adding A Row In Formula Using Vba
My problem is that I want to select a cell with a "Sum" formula ex.

View Replies!   View Related
Look For Character In Cell And Insert New Character - For More Than One
I have a cell which will contain SER01+SER02+SER03

and what i need it to contain is [SER01]+[SER02]+[SER03]

and shocker is i've got this to work for the first instance but not the other two

code as below... be grateful for your help

Sub measure1()
Dim list As String, pos As Integer, refl As String, refr As String, newlist As String
list = Cells(1472, 16).Value
pos = InStr(list, "+")
refl = Left(list, pos - 1)
refr = Right(list, pos + 1)
newlist = "[" & refl & "]"
Cells(1472, 17) = newlist
End Sub

View Replies!   View Related
End Of Month, End Of Year Clear Cell
I have a worksheet that keeps a year to date and month to date total of overages or underages (end of day cash reconciliation). I need to have a formula that clears the month or year to date total cell at the appropriate time and I want to do this independant of the year as I generate a new sheet every day and enter yesterday's numbers.

I think I have the year figured out by picking the month and day and comparing but the only thing I have come up with for the month is comparing to a list of 13 dates (leap year).

View Replies!   View Related
Adding A Row In One Sheet Will Auto Add To Another
I am currently working with a very detailed cost sheet that I have created in excel. To keep this description short... I ran out of columns after 6 months and had to create a new tab for the next 6 months. Basically, I have copied and pasted the first six months to the new tab and QC'd all the formulas and all that.. What I am foreseeing happen, as I've seen this in the past, is this cost sheet is a dynamic thing that will require the addition of rows for more line items.

My question is this, is there a way to "link" the two tabs so that if I added a row to the one sheet, it will automatically add a row to the 'linked' sheet in the exact same position with the same formatting?

View Replies!   View Related
Adding A Formula Upto The Last Row Of Data
Every day i have to pull of a text document from a server and import it into excel so i can change the format of certain columns. Then import the data into a database. I am trying to create a macro that will enable me to make these changes instantly without having to spend minutes on them.

There is once column that has the format 2009-03-12-10:02:09 which indicates date and time however there is a - inbetween them. I know that i can use =DateValue(left(a1,10))+ Time alue(right(a1,8)) and then format the cells to date and time to remove the -. My issue is that i only want to use the formula upto the last row of data otherwise i end up importing blank rows into my database.

View Replies!   View Related
Adding A New Row To A List When Filter Is Applied
If filtering column C, just showing 'A's, by pressing the add row button, I want to copy the last cell in column C and pasting it in row 73, since it's the next unused cell when the worksheet is unfiltered.

View Replies!   View Related
Adding A Blank Row In Between Excisting Rows
This is what I have:

A-------------B
gfdfd------sjfsdnjsnd
gfdfd------sjfsdnjsnd

This is what I need:

A-------------B
gfdfd------sjfsdnjsnd
....

I just need a black row between every row that I have now. I'm sure its simple but I can't seem to figure it out. I have tried a macro but don't really know how to work them, it just made a new line in the same spot.

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