Hi guys,
Please help me remove those extra "/" signs between the cities..
Thanks
Hi guys,
Please help me remove those extra "/" signs between the cities..
Thanks
Try this instead...
=SUBSTITUTE(TRIM(A2&" "&B2&" "&C2&" "&D2)," ","/")
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Ford - is that going to put a forward slash between New and York?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
hmm yup it does
it displays "New York" as "New/York"
Other than that works fine.
hi alipezu. without using programming, try:
=SUBSTITUTE(IF(A2<>"","/"&A2,"")&IF(B2<>"","/"&B2,"")&IF(C2<>"","/"&C2,"")&IF(D2<>"","/"&D2,""),"/","",1)
if you have a lot of columns, you might want to consider using VBA. a member of the forum, tigeravatar came up with this:
http://www.excelforum.com/tips-and-t...geravatar.html
Post #8 should be the final code you can use.
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
To run the Excel VBA code:
use:
=ConcatAll(IF(A2:D2<>"",A2:D2,""),"/")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
OK this got messy...
=SUBSTITUTE(A2&IF(A2="","","/")&B2&IF(B2="","","/")&C2&IF(C2="","","/")&D2,"/","",COUNTA(A2:D2))
This link http://www.excelforum.com/tips-and-t...geravatar.html is dead..
Although the first method worked fine for me..
Thank you benishiryo
Also thank to FDibbins
sorry. it should be:
https://www.excelforum.com/tips-and-...geravatar.html
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks