LinkBack URL
About LinkBacks
Hello,
Is it possible to add a specific word or text before any number in a cell? For example currently I have 1.2.3.4.5. etc. and I want it to look like Text1.Text2.Text3.Text4.Text5. etc. Hope that makes sense?!!
Cheers,
Mark.
Hello,
Is it possible to add a specific word or text before any number in a cell? For example currently I have 1.2.3.4.5. etc. and I want it to look like Text1.Text2.Text3.Text4.Text5. etc. Hope that makes sense?!!
Cheers,
Mark.
Last edited by hardcoremark; 03-01-2012 at 11:33 AM.
If it is always the same word, you can try this.
=TRIM(LEFT("TEXT"&SUBSTITUTE(A1,".",". TEXT"),LEN("TEXT"&SUBSTITUTE(A1,".",". TEXT"))-4))
in another column copied down. You can then copy and paste special >> values over the original and delete the formula column.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Using a formula:
="Text"&A1
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
My assumption was the 1 cell contains all of "1.2.3.4.5"
You're probably right having reread the post.
Dom
Actually, when I reread the post, I think your (Domski) assumption might actually be the right one... only the OP can tell us.
Hi guys. Thanks for all the replies!!! NBVC you are right!! Domski - sorry I should have made it more clear!
NBVC - that formula works great!! Any idea how I could alter it if I had a word (of varying lengths) after the number? eg. if I currently have in one cell 1. Apple 2. Orange 3. Banana etc. and wanted Text1. Apple Text2. Orange Text3. Banana etc. Really appreciate all the help!!
try:
="Text"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,". ",".")," "," Text"),".",". ")
Thanks NBVC. That works great!! Thanks again for your help. Very much appreciated.
Sorry just one more thing... In some cells I have a paragraph of text before the 1. Apple 2. Orange 3. Banana etc. Is it possible to alter the formula so that it basically ignores all the text before the 1. Apple 2. Orange 3. Banana etc.?
Last edited by hardcoremark; 02-29-2012 at 07:50 PM.
After trying this out further this morning it looks like the formula adds "Text" where there are any spaces. Is it possible to just have "Text" before the numbers and not in the spaces aswell? Thanks again for any help.
Can we start over?
What do you have now and what do you want in the end?
Good idea!!...
I have an excel spreadsheet, of which one column contains a product description and tracklisting. I upload the spreadsheet to a website where the product description and tracklisting uses html. I need to add a line break <br /> to the tracklisting. So currently in my product description I have something like this;
but I need it to look like this;Boston streetpunk!!! Hard, raw, still melodic and with very good vocals! The band did many sampler contributions in the past... Rebellion presents you their first official full-length album with 12 great songs in total!!!... Tracklist: 1. Thomas P Cullity 2. Jinx 3. Go For Broke 4. Last Chance 5. Boulevard Of Broken Dreams 6. Takin' Over 7. Burn 8. Another Round 9. Hell To Pay 10. Street Rule 11. Taken Away 12. City Rats
Thanks again for all your help.Boston streetpunk!!! Hard, raw, still melodic and with very good vocals! The band did many sampler contributions in the past... Rebellion presents you their first official full-length album with 12 great songs in total!!!... Tracklist: <br />1. Thomas P Cullity <br />2. Jinx <br />3. Go For Broke <br />4. Last Chance <br />5. Boulevard Of Broken Dreams <br />6. Takin' Over <br />7. Burn <br />8. Another Round <br />9. Hell To Pay <br />10. Street Rule <br />11. Taken Away <br />12. City Rats
I am thinking that is going to need Regular Expressions to solve... which I am not really an expert of.....
Ok. Thanks anyway. Please let me know if you think of anything I could try or an alternative way around it. Thanks again.
Out of curiosity what is the max number of numbered items in the cells?
Do they always occur as the last part of the cell text string?
It varies but most would have 10-15 tracks (or numbered items in the cells). I can actually edit the products through the admin part of the website but it's painstaking to go through every single product. This way I can upload a batch of products so even if I could only alter those cells that contained a certain number of tracks it would be a massive help.
They would always occur as the last part of the cell text string.
Thanks for the assist Rory
I wish you could get a brain implant for Regular Expressions. They make mine hurt.
Dom
Mine too. For anything other than simple matches, I'd rather be wading through DAX manuals...
Good luck.
Rory, I tried it using Morefunc function : =REGEX.SUBSTITUTE(A1,"([\d]+\.)","<br />$1")
but it is not accepting the last argument.. any ideas why? it seems the $1 part is the part it doesn't like... if I leave it as "<br />", it works except the numbers, obviously are lost.... not sure if there is an alternative pattern to keep the numbers....
That works a treat!! Thanks so much Rory and thankyou NBVC.
alternative without brain surgery:
Please Login or Register to view this content.
Nice one.
Just a small formatting typo - should be
if indeed there is a requirement for a space after all the 'br'sPlease Login or Register to view this content.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Not sure (I don't use morefunc) - might be a different regexp syntax. Does \1 work instead of $1 in the last arg?Originally Posted by NBVC
In HTML there isn't.if indeed there is a requirement for a space after all the 'br's
not, that didn't work.
I've just come back to this thread as I need to use it again and couldn't remember the code. But the post with the answer in (by Rory) seems to have been deleted?!! Can anyone tell me why and if it is possible to somehow find it again?
Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks