Insert characters in spaces within cell ???

I have a long list of words that are appended to the end of a url,
something like this: (one url per cell)

http://www.website.com/default&word word word
http://www.website.com/default&word word
http://www.website.com/default&word word word
http://www.website.com/default&word word word word

I need to put "+" symbols in all spaces between the words, so it will
look like this:

http://www.website.com/default&word+word+word
http://www.website.com/default&word+word
http://www.website.com/default&word+word+word
http://www.website.com/default&word+word+word+word

I'm not sure how to do this, since all the words are different
lengths... is there a formula or something to fill spaces with
characters, or somehting like that???


---
Message posted from http://www.ExcelForum.com/

0
4/15/2004 5:23:07 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
361 Views

Similar Articles

[PageSpeed] 26

Hi
try
=SUBSTITUTE(A1," ","+")
in a helper column. Or to create a hyperlink you may use
=HYPERLINK(SUBSTITUTE(A1," ","+") )

--
Regards
Frank Kabel
Frankfurt, Germany


> I have a long list of words that are appended to the end of a url,
> something like this: (one url per cell)
>
> http://www.website.com/default&word word word
> http://www.website.com/default&word word
> http://www.website.com/default&word word word
> http://www.website.com/default&word word word word
>
> I need to put "+" symbols in all spaces between the words, so it will
> look like this:
>
> http://www.website.com/default&word+word+word
> http://www.website.com/default&word+word
> http://www.website.com/default&word+word+word
> http://www.website.com/default&word+word+word+word
>
> I'm not sure how to do this, since all the words are different
> lengths... is there a formula or something to fill spaces with
> characters, or somehting like that???
>
>
> ---
> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
4/15/2004 5:37:21 PM
Thank you that worked perfectly

--
Message posted from http://www.ExcelForum.com

0
4/15/2004 5:58:39 PM
If these are constants.   Couldn't you just use
Ctrl+H
   replace:    (single space)
            by:    +

 and be done with it.
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"travelmadness >" <<travelmadness.14rmzp@excelforum-nospam.com> wrote  ...
> Thank you that worked perfectly.



0
dmcritchie (2586)
4/16/2004 1:48:37 PM
Reply:

Similar Artilces:

highlight range, apply calculation to data in cells and paste special to same range
I know how to select a range of cells and copy: Range("O2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy but now 1. applying the calculation, which should be value of cell * 1000 2. special past values only to same selected sells ? You enter 1000 in a separate blank cell and copy it then highlight the range to be multiplied and past special-> multiply. that will multiply all the cells by 1000 in the pasted range. Regards, OssieMac "S Himmelrich" wrote: > I know how to select a range of cells and copy: &g...

Stopping autoformat when replacing cells.
I'm trying to replace text cells that read 1-1, 1-2, 1-3, etc. to be 2-1, 2-2, 2-3, etc. I've been going to Find and finding "1-" and replacing with "2-", which changes the 1-1, for example to 38749. What am I doing wrong? Hi Excel is treating 1-1 etc as a date - and hence it comes up with 38749 (which is 1st Feb). I would suggest either formatting the cells to Text before you start or prefixing your entries with ' Andy. "Jaelou" <Jaelou@discussions.microsoft.com> wrote in message news:FBB6D3EA-9DD4-4255-9D21-D760A4D50CBE@microsoft.co...

automatic copy cells into other worksheet??
i am trying to find out how i can type text in an excel cell and have it automatically inserted in another worksheet. is this possible?? if so how?? cheers. Hiiiiiiii dear hope u are ok go the sheet2 cell A1 and type =Sheet1!A1 and Drag it and now go to sheet1 and type something in Cell A1 and A2 and A3 and then check them in sheet2 Colum u will find what u have written hope it may work for uuuuu good luck "Matt Hall" wrote: > i am trying to find out how i can type text in an excel cell and have it > automatically inserted in another worksheet. > is this possible?...

Finding Next Empty Cell in a Range
My worksheet is set-up for 12 month price tracking. The entry range is 12 rows (months) by 31 columns (days). How can I write a macro that will locate the cursor to the next empty cell in the range? What I am looking for is an efficient "find" command. I would prefer to execute by ctl-(letter) if possible. Thanks for helping. Need more information - what last/empty cell are you looking for? Next empty one in a row or column? Do we need to consider what the current month or day is? While we're figuring that out, here's code with 3 separate 'sea...

Select multiple adjacent cells of multiple cells without selecting
Select multiple adjacent cells of multiple cells without selecting adjacent cells one by one. those cells looks like; |adjacent cells |cells| |some characters | A | |some characters | B | |some characters | A | |some characters | A | |some characters | B | I am trying to select adjacent cells of A cells without selecting adjacent cells one by one. Thank you for your help. Adjacent in this case, means? If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgr...

Masking out characters in CEdit
Hi, I need to make a CEdit mask out some characters. Those characters should be deleted. I got a simple solution by creating a CMyEdit, catching WM_CHAR and ignoring those unwanted chars there. Works ok, but there are two problems: It does not work for characters that are not typed, but inserted. And, I have to create a CMyEdit variable for every Edit Control. Are there any simple solutions for that? Thanks for ideas! Christian Wirth In article <cc95755a.0409160531.15c24e80@posting.google.com>, wirthi@gmx.at says... > Hi, > > I need to make a CEdit mask out some characte...

Locking cells #9
I'm using Excel 2003 under Win XP SP3 Is it possible to lock some contiguous cells so they cannot be deleted or changed but may still be moved elsewhere on the worksheet? Ideally I'd like to copy individual cells from the protected block I'm a beginner with Excel so please no fancy VB code TIA Stubass Possible yes but................ Only if the area to which they(it) are being pasted is an unlocked range. After the locked cells are copied to the new area, they will have to be re-locked at that area if you don't want the copied cells to be edited. The originals will r...

Change cell background color based on content that results from li
This could be very simple, but I will lay the groundwork first. I have a schedule spreadsheet that I import data to from a web based program. There are existing filters to remove all formatting of the data and remove that which we do not use. What remains is a non formatted sheet that other workbooks link to so we can produce daily sheets. Now the data that is linked on the other sheets may be for example the number 150. It appears throughout the sheet and I would like to color any cell that contains the number 150. The problem is I can't search for 150 because it reall...

Leave Zero's in cell
Hi, when I put three zero's into a cell,.excel automatically changes it back to one zero. I want to keep the three zero's in the cell. Woiuld appreciate any advice please Regards Ivan Hi Ivan Either: Format the cell as Text (although then the numbers entered are text rather than genuine numerics) or format the cells to show leading zeroes (eg a custom format like 000) - note that this is simply for display purposes as the actual contents of the cell will be 0. Hope this helps1 Richard On 15 Mar, 10:47, "Ivan B" <nos...@nospam.net> wrote: > Hi, > when...

Line-break in a cell without ALT-ENTER?
Is there a way to do this? I would like to make it so when the user hits "ENTER", it doesn't go to the next cell. I want it to just make a line-break in the cell. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ You could use a couple of macros, but before you do that, how would you really hit the enter key? I think I wouldn't really do this, but if had to, I'd remap the enter key from the numeric keyboard only. Then I could still use the QWER...

BULK Conditional Formatting
I've read the posts on conditional formatting for cell colour based o another cell's value (eg. set the conditional formatting to "formul is" and then "=A1>0" and set the colour as red / blue / whatever...) however wondering if I can do this for an entire column withou individually changing the conditional formatting for each cell one b one (as there are over 400 rows). Basically I need a formula that reads the contents of column B for th particular row that is active. Can anyone help -- Rob Moyl ---------------------------------------------------------------...

How do I enter the character '&' in a custom header?
Can anyone help me with this? Use two of them: && toolman wrote: > > Can anyone help me with this? -- Dave Peterson Try && Mike R. "toolman" wrote: > Can anyone help me with this? That works! Thanks for your help! "Mike R" wrote: > Try && > > Mike R. > > "toolman" wrote: > > > Can anyone help me with this? ...

Returning Data from a third cell in same row that meets two other
I'm looking for a formula that will return a result from a cell on a same row as two other cells that meet certain criteria. I'm sure there is a way to do this but i am a novice at this kind of stuff and can't seem to figure it out. For example i want a cell to = what is in column E when column A="36751" and when column B="Total Returns" The spreadsheet has 55000 rows. there will only be one instance where both these criteria are met. i want to use this to create a seperate spreadsheet with just info i need and can update on a daily basis. thanks in ...

Autofit Merged Cells? Can we do this?
I have a row that has merged column cells. Even if I choose autofit, it will not autofit. Is tehre a way to do this? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ If you search the group archives: http://google.com/advanced_group_search?q=group:*excel* using "merge autofit" (without quotes) as search terms, you'll find many posts on this topic, including links to a macro by Jim Rech that may work for you. In article <kenji4861.vwe00@excelf...

How do I remove a hidden space in Excel cells
Not sure if this is an Excel or Word problem... I am doing a mail merge in Word (envelopes). I am pulling my data (name, address, cit/state/zip) from an Excel .xls I created. In mail merge, I select envelopes and go through all the steps. I use the Address Block for the delivery address. However, when it pulls the data over, the name line (first line of address block) is indented by one space. No matter what I do (place cursor at indent and use backspace or place cursor at next two correct lines and add a space), the change doesn't take place. I've checked all the tabs as...

DVD Insert
I have been asked by a friend to design an insert for a DVD he is making. The only templates I've been able to find are the ones that are actually for CD jewel cases. He is going to be using the DVD cases like you see in the stores when you buy a movie, which is much larger and the insert goes into the plastic sleeve. It's about 10-1/2" X 7-1/4". I checked the Microsoft site, but they had nada. Does anyone have a place where I could get a template for this project, or any idea of the best way to set this up? I've done CD inserts, but never a DVD before. Thanks....

how can i present a JPG in a window having cell with the path?
I build a catalog for coins collection with Excel. each row represents a coin (record). in two cells (fields) i have a path to both sides of the coin scanned as a JPG files in the specific directory. i would like the following scenario: when i stand on a specific row (any cell), the relevant images are presented in 2 big windows. when i move to another row, the image automatically changes etc. i would like also to control the size of the window. would appreciate your help, thanks, sam Hi Sam See if this gets you started: Insert an Image1 from the activex coltrols toolbox. S...

Automatically updating cells on other worksheets when data typed into primary sheet
I'm trying to create a workbook that will allow me to enter data on the first sheet and it will then append the data to a second sheet by automatically starting a new line. Example: I enter "This is a test" into A1 on sheet 1 First available line on sheet2 automatically updates with "This is a test" When it comes to enter something new in A1 on sheet1, same function above goes into action, accept it should be on the next available line which will be A2 on sheet2, as A1 is already used by previous data entry. Can this be done? I would appreciate any help with this. ...

insert logo using AUTOCORRECT
Have been trying to insert my company's logo using AUTOCORRECT. It works like a charm in WORD. It doens't work in Excel. ex. when I type "lg" these characters should be replaced by my company's logo. Does anyone know how to do this in Excel? -- digicat ------------------------------------------------------------------------ digicat's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14920 View this thread: http://www.excelforum.com/showthread.php?threadid=497273 ...

Out of Disk Space........ Help!!
Suddenly I am out of disk space. It seems that the IMC OUT Archive has grown so large you cannot even do a dir listing of it. How do I clear the archive files to reclaim space ? Thanks You can safely delete contents of IN\ARCHIVE and OUT\ARCHIVE folders. You also can disable message archival if you do not need it in IMC props\Diag logging tab. froggy wrote: > Suddenly I am out of disk space. It seems that the IMC > OUT Archive has grown so large you cannot even do a dir > listing of it. How do I clear the archive files to > reclaim space ? > > Thanks To help complete...

Counting cells with particular month and year
Thanks to help previously received, I have been using this formula to count the number of referrals received in a particular month. Is it possible to include the year as well? =SUMPRODUCT(--(MONTH(A2:A10)=Stats!A1),--(ISNUMBER(B2:B10))) Now the Year also included in the below formula the year reference cell is referred in B1 cell of STATS sheet. =SUMPRODUCT(--(MONTH(A2:A10)=Stats!A1)*--(YEAR(A2:A10)=Stats!B1),--(ISNUMBER(B2:B10))) Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Saylindara" wrote: ...

A way to increase a number in a cell by using the + key
Hi, i do a lot of data entry and am looking for a way to increase the number in a cell (or range of cells) so that i dont have to enter the new number in each time. I have columns that separate items and i use the sheet to show the total count for each item in it's row. eg Item 1 Item 2 Item 3 2 5 4 I'd like to use the + key only to increase the counts for each item. Did i make sense?? It's a Friday and my mind isn't quite up for a lot of detailed explanations, lol. Any assistance would be appreciated. Tim That would require VBA How far are you will...

Double Space in notes in contacts.
Sometimes when hitting enter in a contact note field, enter double spaces instead of single space. Anyway to get it to get it to single space when hitting enter? -- Thank you, Eric Vogel ...

Comparing cell values then labeling them based on comparision
Hi. I'm trying to find a function or way to compare two cells and then label them according to if one cell is lesser, greater, or the same as the other. I have a large data sheet where I want to compare many pairs of cells and then have them labeled in this way. For example for each pair where the second cell value is larger than the first it would put greater, where the first is larger it would be lesser, etc. Can anybody help me with this? also feel free to email me at andrew@metiri.com. Thanks so much for your help. > Hi. I'm trying to find a function or way to compar...

Insert hyperlink greyed out
Can't see any other posts on the above any advice valued. Thanks {XP2, with Publisher 2002} On Sun, 23 Oct 2005 08:39:37 +0100, Gel wrote (in article <1130053177.481108.189290@o13g2000cwo.googlegroups.com>): > Can't see any other posts on the above any advice valued. > Thanks > > {XP2, with Publisher 2002} > Ah yes, "can't see' the classic euphemism for "I'm a lazy f**kwit, I can't be arsed to RTFH, or to STFW. Please wipe my arse for me, because I'm too lazy to find the bog roll..." Where are you attempting to insert th...