How to convert "/" to "-"

K

Kichan

Board Regular
Joined
Feb 21, 2022
Messages
67
Office Version
  1. 2010
Platform
  1. Windows
This is my queation to be cleared

Book1.xlsx
AB
1QuestionAnswer Should Be
21/1,2,3,2/A,B,C1-1,1-2,1-3,2-A,2-B,2-C
3850/A-B,C,D850-A-B,850-C,850-D
450/8,6,2/5,1,4/5,550-8,50-6,2-5,2-1,4-5,4-5
51000/5-2,6-8,1-91000-5-2,1000-6-8,1000-1-9
Sheet1


I got some formula that a friend from here has given me below, But it needs some modifications to get the above answer

1-1,2,3,5-1,2,3 to 1-1,1-2,1-3,5-1,5-2,5-3.xlsx
ABCDEF
1QuestionAnswer
21-1,2,3,2-A,B,C1-1,2,32-A,B,C2-A,B,C 1-1,1-2,1-3,2-A,2-B,2-C
3850-A,B,C,D850-A,B,C,D850-A,B,C,D  850-A,850-B,850-C,850-D
450-8,6,2,2-5,1,4-5,550-8,6,22-5,1,4-5,52-5,14-5,550-8,50-6,50-2,2-5,2-1,4-5,4-5
51000-5,2,61000-5,2,61000-5,2,6  1000-5,1000-2,1000-6
Sheet1
Cell Formulas
RangeFormula
B2:B5B2=TRIM(LEFT(SUBSTITUTE(A2,",",IF(ISNUMBER(FIND("-",A2,FIND("-",A2)+1)),REPT(" ",100),","),LEN(LEFT(A2,FIND("-",A2&"-",FIND("-",A2)+1)))-LEN(SUBSTITUTE(LEFT(A2,FIND("-",A2&"-",FIND("-",A2)+1)),",",""))),100))
C2:C5C2=SUBSTITUTE(A2,TRIM(LEFT(SUBSTITUTE(A2,",",IF(ISNUMBER(FIND("-",A2,FIND("-",A2)+1)),REPT(" ",100),","),LEN(LEFT(A2,FIND("-",A2&"-",FIND("-",A2)+1)))-LEN(SUBSTITUTE(LEFT(A2,FIND("-",A2&"-",FIND("-",A2)+1)),",",""))),100))&",","")
D2:D5D2=SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(C2,",",IF(ISNUMBER(FIND("-",C2,FIND("-",C2)+1)),REPT(" ",100),","),LEN(LEFT(C2,FIND("-",C2&"-",FIND("-",C2)+1)))-LEN(SUBSTITUTE(LEFT(C2,FIND("-",C2&"-",FIND("-",C2)+1)),",",""))),100)),B2,"")
E2:E5E2=IF(D2="","",MID(SUBSTITUTE(SUBSTITUTE(A2,B2&",",""),D2,""),2,99))
F2:F5F2=SUBSTITUTE(TRIM(SUBSTITUTE(B2,",",","&LEFT(B2,FIND("-",B2)))&" "&IFERROR(SUBSTITUTE(D2,",",","&LEFT(D2,FIND("-",D2))),"")&IFERROR(" "&SUBSTITUTE(E2,",",","&LEFT(E2,FIND("-",E2))),""))," ",",")



Thank you
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
paste the function into a module, then use:
=FixTxt(A1)

Code: 
Public Function FixTxt(ByVal pvVal)
On Error Resume Next
FixTxt = Replace(pvVal, "/", "-")
End Function
 
Upvote 0
This is what I have.
Book1
ABCDEF
1QuestionAnswer
21/1,2,3,2/A,B,C1/1,2,32/A,B,C2/A,B,C 1-1,1-2,1-3,2-A,2-B,2-C
3850/A-B,C,D850/A-B,C,D   850-A-B,850-C,850-D
450/8,6,2/5,1,4/5,550/8,62/5,1,4/5,52/5,14/5,550-8,50-6,2-5,2-1,4-5,4-5
51000/5-2,6-8,1-91000/5-2,6-8,1-9   1000-5-2,1000-6-8,1000-1-9
Sheet2
Cell Formulas
RangeFormula
B2:B5B2=TRIM(LEFT(SUBSTITUTE(A2,",",IF(ISNUMBER(FIND("/",A2,FIND("/",A2)+1)),REPT(" ",100),","),LEN(LEFT(A2,FIND("/",A2&"/",FIND("/",A2)+1)))-LEN(SUBSTITUTE(LEFT(A2,FIND("/",A2&"/",FIND("/",A2)+1)),",",""))),100))
C2:C5C2=IFERROR(SUBSTITUTE(A2,TRIM(LEFT(SUBSTITUTE(A2,",",IF(ISNUMBER(FIND("/",A2,FIND("/",A2)+1)),REPT(" ",100),","),LEN(LEFT(A2,FIND("/",A2&"-",FIND("/",A2)+1)))-LEN(SUBSTITUTE(LEFT(A2,FIND("/",A2&"/",FIND("/",A2)+1)),",",""))),100))&",",""),"")
D2:D5D2=IFERROR(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(C2,",",IF(ISNUMBER(FIND("/",C2,FIND("/",C2)+1)),REPT(" ",100),","),LEN(LEFT(C2,FIND("/",C2&"/",FIND("/",C2)+1)))-LEN(SUBSTITUTE(LEFT(C2,FIND("/",C2&"/",FIND("/",C2)+1)),",",""))),100)),B2,""),"")
E2:E5E2=IF(D2="","",MID(SUBSTITUTE(SUBSTITUTE(A2,B2&",",""),D2,""),2,99))
F2:F5F2=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(B2,",",","&LEFT(B2,FIND("/",B2)))&" "&IFERROR(SUBSTITUTE(D2,",",","&LEFT(D2,FIND("/",D2))),"")&IFERROR(" "&SUBSTITUTE(E2,",",","&LEFT(E2,FIND("/",E2))),""))," ",","),"/","-")


Hopefully this is what you are looking for. If it's not, please provide clarification.
 
Upvote 0
ranman256 said:
paste the function into a module, then use:
=FixTxt(A1)

Code: 
Public Function FixTxt(ByVal pvVal)
On Error Resume Next
FixTxt = Replace(pvVal, "/", "-")
End Function
Click to expand...
Ranman,

I think you need to look at the examples more closely. It is NOT a simple replacement/substitution. So I am pretty sure your code won't work.
Look at the Answer as compared to the Question (all the columns in between appear to be temporary work columns).

mackc557
Looking at the example myself, I cannot say I fully understand the logic you are trying to apply.
There seem to be a lot more than just replacing all "/" with "-" going on. It looks like some numbers are being distributed to the other numbers.
An explanation of the logic that should be applied would be most helpful. Don't leave us to "guess" as to what the logic is.
 
Upvote 0
Joe4 said:
Ranman,

I think you need to look at the examples more closely. It is NOT a simple replacement/substitution. So I am pretty sure your code won't work.
Look at the Answer as compared to the Question (all the columns in between appear to be temporary work columns).

mackc557
Looking at the example myself, I cannot say I fully understand the logic you are trying to apply.
There seem to be a lot more than just replacing all "/" with "-" going on. It looks like some numbers are being distributed to the other numbers.
An explanation of the logic that should be applied would be most helpful. Don't leave us to "guess" as to what the logic is.
Click to expand...

Thank you for looking into it, peer review is always useful to catch unseen errors. As to my logic, it was simply take the OP's solution, changed what is in the find code, let it run as normal, and replace the "/" with "-" at the end. I have tested it with the provided Questions and received the provided Answers.

I do not know the use or scope of the project, but personally I feel it is a very limited way of doing this, as any more than three groups like this will likely not evaluate. My natural instinct is to rewrite the entire thing to either be more flexible or more consolidated. I'm learning that sometimes projects don't need that large amount of flexibility, but it's an observation.
 
Upvote 0
Whoops,
Sorry, my question should have been directed to Kicham, not mackc557.
I was confusing your post for the OPs.
 
Upvote 0
mackc557 said:
This is what I have.
Book1
ABCDEF
1QuestionAnswer
21/1,2,3,2/A,B,C1/1,2,32/A,B,C2/A,B,C 1-1,1-2,1-3,2-A,2-B,2-C
3850/A-B,C,D850/A-B,C,D   850-A-B,850-C,850-D
450/8,6,2/5,1,4/5,550/8,62/5,1,4/5,52/5,14/5,550-8,50-6,2-5,2-1,4-5,4-5
51000/5-2,6-8,1-91000/5-2,6-8,1-9   1000-5-2,1000-6-8,1000-1-9
Sheet2
Cell Formulas
RangeFormula
B2:B5B2=TRIM(LEFT(SUBSTITUTE(A2,",",IF(ISNUMBER(FIND("/",A2,FIND("/",A2)+1)),REPT(" ",100),","),LEN(LEFT(A2,FIND("/",A2&"/",FIND("/",A2)+1)))-LEN(SUBSTITUTE(LEFT(A2,FIND("/",A2&"/",FIND("/",A2)+1)),",",""))),100))
C2:C5C2=IFERROR(SUBSTITUTE(A2,TRIM(LEFT(SUBSTITUTE(A2,",",IF(ISNUMBER(FIND("/",A2,FIND("/",A2)+1)),REPT(" ",100),","),LEN(LEFT(A2,FIND("/",A2&"-",FIND("/",A2)+1)))-LEN(SUBSTITUTE(LEFT(A2,FIND("/",A2&"/",FIND("/",A2)+1)),",",""))),100))&",",""),"")
D2:D5D2=IFERROR(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(C2,",",IF(ISNUMBER(FIND("/",C2,FIND("/",C2)+1)),REPT(" ",100),","),LEN(LEFT(C2,FIND("/",C2&"/",FIND("/",C2)+1)))-LEN(SUBSTITUTE(LEFT(C2,FIND("/",C2&"/",FIND("/",C2)+1)),",",""))),100)),B2,""),"")
E2:E5E2=IF(D2="","",MID(SUBSTITUTE(SUBSTITUTE(A2,B2&",",""),D2,""),2,99))
F2:F5F2=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(B2,",",","&LEFT(B2,FIND("/",B2)))&" "&IFERROR(SUBSTITUTE(D2,",",","&LEFT(D2,FIND("/",D2))),"")&IFERROR(" "&SUBSTITUTE(E2,",",","&LEFT(E2,FIND("/",E2))),""))," ",","),"/","-")


Hopefully this is what you are looking for. If it's not, please provide clarification
Click to expand...
Perfect Brother.. It is working for me ?? .. thank you very much ..
 
Upvote 0
Kichan said:
Perfect Brother.. It is working for me ?? .. thank you very much ..
Click to expand...

mackc557 said:
This is what I have.
Book1
ABCDEF
1QuestionAnswer
21/1,2,3,2/A,B,C1/1,2,32/A,B,C2/A,B,C 1-1,1-2,1-3,2-A,2-B,2-C
3850/A-B,C,D850/A-B,C,D   850-A-B,850-C,850-D
450/8,6,2/5,1,4/5,550/8,62/5,1,4/5,52/5,14/5,550-8,50-6,2-5,2-1,4-5,4-5
51000/5-2,6-8,1-91000/5-2,6-8,1-9   1000-5-2,1000-6-8,1000-1-9
Sheet2
Cell Formulas
RangeFormula
B2:B5B2=TRIM(LEFT(SUBSTITUTE(A2,",",IF(ISNUMBER(FIND("/",A2,FIND("/",A2)+1)),REPT(" ",100),","),LEN(LEFT(A2,FIND("/",A2&"/",FIND("/",A2)+1)))-LEN(SUBSTITUTE(LEFT(A2,FIND("/",A2&"/",FIND("/",A2)+1)),",",""))),100))
C2:C5C2=IFERROR(SUBSTITUTE(A2,TRIM(LEFT(SUBSTITUTE(A2,",",IF(ISNUMBER(FIND("/",A2,FIND("/",A2)+1)),REPT(" ",100),","),LEN(LEFT(A2,FIND("/",A2&"-",FIND("/",A2)+1)))-LEN(SUBSTITUTE(LEFT(A2,FIND("/",A2&"/",FIND("/",A2)+1)),",",""))),100))&",",""),"")
D2:D5D2=IFERROR(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(C2,",",IF(ISNUMBER(FIND("/",C2,FIND("/",C2)+1)),REPT(" ",100),","),LEN(LEFT(C2,FIND("/",C2&"/",FIND("/",C2)+1)))-LEN(SUBSTITUTE(LEFT(C2,FIND("/",C2&"/",FIND("/",C2)+1)),",",""))),100)),B2,""),"")
E2:E5E2=IF(D2="","",MID(SUBSTITUTE(SUBSTITUTE(A2,B2&",",""),D2,""),2,99))
F2:F5F2=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(B2,",",","&LEFT(B2,FIND("/",B2)))&" "&IFERROR(SUBSTITUTE(D2,",",","&LEFT(D2,FIND("/",D2))),"")&IFERROR(" "&SUBSTITUTE(E2,",",","&LEFT(E2,FIND("/",E2))),""))," ",","),"/","-")


Hopefully this is what you are looking for. If it's not, please provide clarification.
Click to expand...
Hi.. There is a small problem brother the range that I am getting from this formula is limited from 1 to 35 (55/1,2,3,.....35) only if Ihave to get 110 how to do it bro

Question : 170/1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37
Answer : 170-1,170-2,170-3,170-4,170-5,170-6,170-7,170-8,170-9,170-10,170-11,170-12,170-13,170-14,170-15,170-16,170-17,170-18,170-19,170-20,170-21,170-22,170-23,170-24,170-25,170-26,170-27,170-28,170-29,170-30,170-31,170-32,170-33,170-34,170-35

after 35 it is not working
3-5-8 to 3-5,6,7,8.xlsm
ABCEL
11/1-41/1,2,3,42/6-98/11-131-1,1-2,1-3,1-4,2-6,2-7,2-8,2-9,8-11,8-12,8-13
2170/1-35170/1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35170-1,170-2,170-3,170-4,170-5,170-6,170-7,170-8,170-9,170-10,170-11,170-12,170-13,170-14,170-15,170-16,170-17,170-18,170-19,170-20,170-21,170-22,170-23,170-24,170-25,170-26,170-27,170-28,170-29,170-30,170-31,170-32,170-33,170-34,170-35,170-
Sheet1
Cell Formulas
RangeFormula
L1:L2L1=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(H1,",",","&LEFT(H1,FIND("/",H1)))&" "&IFERROR(SUBSTITUTE(J1,",",","&LEFT(J1,FIND("/",J1))),"")&IFERROR(" "&SUBSTITUTE(K1,",",","&LEFT(K1,FIND("/",K1))),""))," ",","),"/","-")
B1:B2B1=grinson(A1)


please take a look bro..
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Combining formulae for UK postcodes
2
Replies
10
Views
666
Fluff
Fluff
V
  • Solved
SUBSTITUTE function
Replies
3
Views
263
Fluff
Fluff
A
  • Solved
Remove last character if it is a number
Replies
3
Views
369
Fluff
Fluff
T
  • Solved
Challenge Question: A Quest for Elegance
Replies
6
Views
529
Peter_SSs
Peter_SSs
C
  • Solved
Multi Line Cell - Find Text String
Replies
3
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top