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 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
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
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
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
Adding Time: Adding Individual Cells Works, But SUM Doesn't
I have a column of times: e.g. 10:03:00 and I would like to add them all up.

=A1+A2 works fine.

=sum(A1:A10) does not.

View Replies!   View Related
Adding Dim'd Variables: Ignoring Strings & Adding Number
I've got a problem involving several Dim'd Variables needing to be added up, they're all Dim'd as Variants though as they can be either strings or numbers at any time.

I need a formula (VBA) to add them up (to add their actual numeric values - not just a "1" if they contain a number) and ignore them all together if they contain string values.

View Replies!   View Related
Adding Data With Userform: Check For Duplicate Before Adding
I have a userform that I'm using to add data to a worksheet, with the following

Private Sub CommandButton1_Click()
Dim OutSH As Worksheet
Set OutSH = Sheets("Sheet1")

OutSH.Cells(nextrow2, 1).Value = Surname.Value
OutSH.cells(nextrow2,2).value = ID.value
OutSH.cells(nextrow2,3).value = Date.value
......
I need to ensure that duplicate entries are not made for the same person on the same date. The ID is unique to each person.

IF statement that can check for a duplicate and then come up with a dialouge box with some custom text, and then exiting the sub?

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
If Then Containing Character
I would like to create an If Then statement in which the condition is based on if a string is a simple text or a formula. Some of my cells contain a formula and some contain simple text...and I would like to cycle through all of them- only do something different to them based on what the cell contains.

View Replies!   View Related
Recognises What Character Is That
do you know any function that recognises which character is in a particular cell?


View Replies!   View Related
Add Alpha Character
I want to add a letter to the end of value and have it start over at "A" when the cell value changes. For example


My Column contains
BIR1510
BIR1522
BIR1524
BIR1524
BIR1524
BIR1524
BIR1532
BIR1532
BIR1533

I want this in another column
BIR1510A
BIR1522A
BIR1524A
BIR1524B
BIR1524C
BIR1524D
BIR1532A
BIR1532B
BIR1533A

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
Is There A Substitute Character That Can Be Used
I have a whole bunch of dates in a column that look like this:

5/01/1998 when it is supposed to be 5/01/2098 is there a character you can use when you are using the find & replace mode i.e.

*/**/20** I know it is not the asterisk but I believe there is some other character that can be used and it won't change any other of the numbers except the 19. I tried reformating and it does'nt work because of the way they sent it to me.

View Replies!   View Related
Nth Character From String
I would like to get the nth character from a string. For example, let's say there is a string "jupiter", and I would like to get the 3rd character of this string, "p". Is there a function in excel vba that would allow me to do this?

View Replies!   View Related
Replace Character With Another
I'm trying to go through a short list of letters and change the contents of every cell in the list that contains a "c" into a "M". I've written this code, but I get a #Value! message on my spreadsheet when I run it:

Function FindCpmDpm(SearchRng As Range)

Dim CelRng As Range

For Each CelRng In SearchRng

If CelRng.Value = "c" Then
CelRng.Value = "M"
End If

Next CelRng

End Function

I am having trouble accessing the cell which contains the "c".

View Replies!   View Related
Position First Character
I have some cells with some spaces and then text where I want to find the position of the first letter. Do any of you know about any function I can use?

The example below contain 5 spaces and then my name. It should give me the answer 6.

" Magne"

View Replies!   View Related
Character Limits
I copied this from Excel:

Length of cell contents (text) 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.

I have an Address Book from an old Personal Information Manager that exports the file as ASCII.

My problem is when I view the .asc file in Excel, Excel has created two rows for several entries.

That creates a problem as I wanted to select Column "A" then do Text to columns with a comma delimiter (the asc files has the comma in place)
However, since Excel put in the second line, I'll not be able to put in a header column.

When I look at the .asc file there is no way that there is more than 32,767 characters in each row. So why did Excel split it up? ....

View Replies!   View Related
Character Limit?
I'm adding some necessary code to the beginning and end of some cell contents in Format | Cells | Custom and I seem to be coming up against a character limit.

I'm using the phrase "!!<"@">!!" to add the code, but with some cell contents the result is ########### (but much longer), and I'm having to remove some of the text to make it work. The limit seems to be around 255 characters.

View Replies!   View Related
Add A Character To Every Cell..
i have a column that is 1900 long and i need to add a "comma" to the end of everything in each cell in that column. How do i go about this? so at the moment it look like this:

random1
random2
random3
random4

But i need to look like this:

random1,
random2,
random3,
random4,

View Replies!   View Related
Character Mark In A Box
I've searched for this in the forums but can't find anything. I need to find/replace a character in Excel, it's a question mark in a box and looks like this

I've tried searching for special characters (using the ALT key and number pad) but I can't find anything matching this.

When I save the spreadsheet as a CSV (TAB delimited) this character gets picked up as a TAB, but when I try find replace on the TAB character it doesn't work!

I'm using Excel 2007.

View Replies!   View Related
Word After Last Specified Character
I am new to Excel and just starting with formulas. I am wondering if a formula can handle the following:

We have a column that has a url in each cell. Example (I removed the http:// before it so that all the words show up and it doesn't change to a clickable link):

i25.photobucket.com/albums/c54/bigbusy/S_Images/sylvantropicalbirdi.jpg[/url]

We would like to tell it to copy the portion after the last forward slash and until the period and put it in another cell. In the above example it would give us sylvantropicalbirdi. We then would want to append a _t to it.

Is this possible or am I just wasting my time trying to figure it out?

View Replies!   View Related
Cells Containing Specified Character
What formula should I use to search for cells which have a certain single letter or digit.

Ex. Those with the letter O

2007-FHYO-45 YES
2006-FHY-45 NO
2007-FYY-38 NO
2007-FYY-21 NO
2007-FGHO-81 YES

View Replies!   View Related
Sum Character Frequency
I have large spreadsheet that analyses the number of times specific characters etc appear in individual cells, but only takes into consideration the filtered cells. My current formula that works fine is:

= sumproduct((subtotal(3,offset($c$66,row($c$66:$c$6959)-min(Row($C$66:$c$6959)),,1))),--isnumber(search("F",$s$66:$s$6959)))

So at the moment every time an F or other specified values is in one of the filtered cells it is counted and displayed in the respective cells. What I would like it to be able to do is recognise when a cell has say 3 F's, and increment the displayed product accordingly.

View Replies!   View Related
Looking For 'X' Or 'Z' As The 5th Character In A String
I'm looking for some VBA code that would look at a column of data, look at each cell value in that column and if the cell value starts with the letter 'G' and the 5th character in that cell is not 'Z' or 'X' I would like to highlight that cell. Example:G123-123 would be highlighted whereas G123Z-123 would not.

View Replies!   View Related
Replace Nth Character
I need to replace the sixth character in each row of a column within an excel spreadsheet and wanted to know how to do this within vba and if anybody had any example code

View Replies!   View Related
Test To See If A Character Is A Number
I have a number of entries in column A.
The format is:

A1 B680192 Middle School
A2 Office Building Project

and so on;

I want to test if char.s 2 through 7 are numbers.

I can seperate one char at a time and use CODE function to check if the result is between 48 and 57.

I know how to split all six char.s at a time e.g. 680192, but I don't know how to test to see if it is a number?

ISNUMBER function doesn't work.

If I can do it with excel function, it would be better. If VBA has to be used, so be it.

View Replies!   View Related
Macro To Hide CHR(10) Character
I have the code below, which works fine apart from I get a little square coming up every time I run the code, as this gives you a line the Chr(10) has to be there. When I tale the Chr(10) out the little square disappears but then I don't get a new line. I have set the formatting to "wrap text" but it is still showing up. I am not quite sure where to go from here, I have also done a ="test"&CHAR(10)&"test" and that worked fine with no little square.

View Replies!   View Related
Countif V. 255 Character Limit?
I'm trying to use the countif function to search a cell containing, say, 1000 alphanumeric characters.

For example, cell A1 contains the text...

"There needs to be an easier way to build worksheet formulas for 64 levels of nesting and 255 arguments to be meaningful. Perhaps a "Formula Composer" dialog box or something that allows you to build up a complex formula in parts and allows you to evaluate the partial formulas. When you're done, and the formula is displayed in the sheet, all of the parentheses would be automatically put in the correct places."

...and cell A2 contains the formula...

View Replies!   View Related
Wildcard Character In IF Condition
I have a little problem in using wildcard character in IF condition.

eg:
--------------------------
For I = 2 to LastRow
If Activesheet.cells(I,5).Text = "ENF*" then
...........................
...........................
...........................
End If
Next I
--------------------------

I want the code to search for any word starting with ENF and then take the necessary action.

Could anyone tell me how to make use of wildcards in a situation like this.

View Replies!   View Related
How To Use Mid Function Based On The 2nd ( X ) Character
I have random descriptions that have the case dimentions within the text
The descriptions are NOT standard length.
the only thing I can think of is the dimensions in ALL descriptions have this in common:

Length ( x ) Width ( x ) Height

I am looking for the Height value
How can I use the =mid function based on the 2nd ( x ) character?


View Replies!   View Related
Change Character Macro
In column A if have strings like this: X-100-X10 or C-100-1X00 etc.

I need to change the X at the end of the string to "." e.g. X-100-.10 or C-100-1.00

Can't figure it out since there can be 1 "X" or more than 1 "X" and the second "X" that needs to get changed can be in a different location i.e. not alway 3 from the right or 7 from the left.

Is there some macro code I can use to make this change? Otherwise I have to change hundreds of these manually.

View Replies!   View Related
Special Character Remove
I have sentences which contains special characters (mentioned below) in a single column. I need to remove all special characters other than space. Could anyone help me on this...

!@#$%^&*()-_=+{}[]|~`?/,:;"

View Replies!   View Related
Extract First Character Of String
I have a column that has various codes such as "E123" and "X456" and "S345". I just want to extract the numbers and leave the letters in each cell. How do you write a formula using the LEFT function to extract the last three numbers?


View Replies!   View Related
Symbol Appear When I Type A Certain Character
I am trying to create a document and i'm not sure if this is possible or not, but i'd like to type in a number in column A (1-10) and then in column B have a symbol appear corresponding to the number.

So if i type in 5 in Column A, Column B will have 5 squares pop up (or something along those lines), etc.

View Replies!   View Related
Add Character Into Empty Cells
I've got a worksheet from a contact details with some empty cells in it, since not everyone has fax number or POBOX, therefore that particular cell is left empty.

Is there any way to replace or put a "-" dash character or whatever into that empty cell so that the exported CSV file is not left with too many trailing commas ?

I'm looking forward for the reply.

[this CSV is to be use by Exchange Server 2007 Powershell script to import Mail-Contact from a CSV file, that's why not all of the contact entity has the parameter defined]

View Replies!   View Related
ASCII Code For Character
Find the ASCII character code for the characters in cells A3-D3? I have a problem importing data into the database because of these cells. They have some weird properties: ISBLANK would return them FALSE; however if I leave blank space in "Find and Replace" it will find and replaces those cells together with cells which are genuinely blank. So I could theoretically go and replace all blank cells to some character (for example ^), then do the opposite and replace ^ into blank and it would solve the problem, but I am curious as to what exactly this character is.

View Replies!   View Related
Split Text After Specified Character
You know how you can combine different cells and concatenate their values? Can you reverse that? For Example, I have this value "Yambao, Mikhail P." and I want to break them apart so I can use "Yambao" as a reference value to fill up other information concerning that name.

View Replies!   View Related
Maximum Character Input
Is their a maximum as to how many characters/words can be fit into one cell on excel 2000? if so what is the limit? and is their anyway to increase this limit?

View Replies!   View Related
VB Script To Remove A Character
I need a VB script to remove a character.

The character is the SECOND . in the following strings
1.07.66
0.59.53
1.08.99
1.10.1
0.58.6

Basically, 1.07.66 represents 1 min and 7.66 sec, but I cant use the data until excel can recognise it as 1.0766

A vb sript would be nice, but a formula is also acceptable.

View Replies!   View Related
Limit First Character To Alpha
I'm trying to have the macro limit the cell entry to 5 characters beginning with a "P" and the remaining 4 character being numeric. The problem I'm having is that it's not recognizing the requirement for the beginning "P" but only identifies if the length is less than 5 characters. Any idea how I can resolve this?

For PN2 = 43 To 72
If Not IsEmpty(Cells(PN2, 1)) And Left(Cells(PN2, 1), 1) "P" And Len (Cells(PN2, 1)) 5 Then
MsgBox ("The employee number must be 5 characters long begining with P (e.g. P0001), please change accordingly!")
Range(Cells(PN2, 1), Cells(PN2 + 2, 1)).ClearContents
Cells(PN2, 1).Select
End If
Next

View Replies!   View Related
Searching A Cell Value For A Character
I want to test a cell for the type of value as 678/256 or 345/872/098/987 etc. The common character in each would be the "/". First, I need to check the cell for this, then take the first number and compare it to an integer(if it is <> , or = to). I hope I am clear with my situation.

View Replies!   View Related
Finding A Character Using If Function
I have several property codes in column E e.g THA 134,THB 224, C 122, 223 AD. The letters A, B, C, D denote a type of property but do not always appear in the same place in the cell (sometimes 3rd character, sometimes first character etc). I want to put in a formula in column F, so that if the character A, B, C or D is in column E then the result in column F will be "A type", "B Type" etc....

View Replies!   View Related
Splitting Character String
How do I split the below character or similar strings into 2 columns of 25 characters each without cutting a word off?

BONE CURETTE-RVERSE ANGLE 5.5MM WIDTH-MEDIUM/BAYONETED

View Replies!   View Related
Deleting New Line Character
A have a worksheet in excel which was copied from pdf.

in each cell number of different lines, the data appears on a new lines. the same as using the new line character. (ALT + ENTER)

The document is huge. well over 3000 individual cells, and generally 4 or 5 lines in each one. Is there anyway i can globally change the whole documents. Just deleteling all the new lines??

Using excel 2007 on windows xp..

View Replies!   View Related
Remove Forbidden Character
I'm trying to write a UDF that takes the text in a cell and returns text with a list of forbidden characters removed from it. At the moment I have the following:

View Replies!   View Related
How Can I Look For A Character Or An Operator In A FORMULA
Iīm trying to find a way to get a formula which can look if there is a character (letīs say it is "-") in a formula which is, by the way, variable (letīs say each cell has different values like: =899+59-6 and so it goes and goes), and brings me a true or false value.

Specifically it is like this:

I have formulas to be evaluated like the following:

=899+59-6

I need a formula which can look into the formula and if it finds the character "-" or a "+", brings me a true or false message.

View Replies!   View Related
Read The 1st Character In The Hex Code
I have a set of Hex Code a total of 4 Sets :

A4EC 9ACF 4A02 044E

1. What i need to do is Read the 1st Character in the Hex Code which say A now.

2. Now I need to find if there is another A in this Hex Code. If yes - e.g
A4EC 9A - I need to find the difference between A-A. How many characters are in between the 2 A-A. So for this I have A 4EC9 A so the difference is 4.
2.1 - Another Senario of "A" - A 4EC9ACF4 A the difference is 8 characters

3. Next we will move on to the next Letter which is 4 and do our search.

The key is i only want to record those Hex code which have a difference of 4,8,10,12 differences.

So for that above example I should be able to record the following :

A 4EC9 A == 4 letter difference
4 A020 4 == 4 letter difference
A 4EC9ACF4 A == 8 letter difference
4 EC9ACF4A020 4 == 10 letter difference
E C9ACF4A02044 E == 12 letter difference

View Replies!   View Related
Evaluate First Character Of String In VBA
How can I evaluate just the first part of these stirings so I can just do something like Left("Ca",2)?

View Replies!   View Related
Replacement Of Suffix Character
We have two characters : One debit shown as Dr
Another Credit shown as Cr

we have an excel sheet containing a problem as under:
row 1 row2
column 1 Sales 345670 Dr
column 2 Receipt 340000 Cr
column 3 TDS 5670 Cr

We have replaced the Suffix so that it looks as under:

Sales 345670
Receipt 340000 Cr
TDS 5670 Cr

Wewant the last two lines to have figures in the negative ie - 340000
- 5670
i.e. cr suffix change to negative figure -340000

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