How to solve this problem?

[Vengeancez]

Hi all,

I'm facing a problem in excel wish i couldn't find a solution or formula to it.

For says my data as listed below.

5487978 (7 numbers)
62481676 (8 numbers)
487-6214 (7 numbers)
9885462 (7 numbers)
63784659 (8 numbers)
8544064 (7 numbers)
541-2578 (7 numbers)

I want to add an additional of a number 6 to those data with 7 numbers only.

What formula can i use in excel to do this?

(please note that there are some - in those numbers, it may affect the formula)

Thanks for the help.

 

[Vengeancez]

i've figure out already,

Very grateful for your help! good day

 

[CyFoo]

welcome

 

[Rick Rothstein]

i've figure out already

If you are still looking for a formula solution, I am pretty sure this one works...

=SUBSTITUTE(LEFT(6,LEN(LEFT(A1,FIND("/",A1)-1))=8)&LEFT(A1,FIND("/",A1)-1)&"/"&LEFT(6,LEN(MID(A1,FIND("/",A1)+1,99))=8)&MID(A1,FIND("/",A1)+1,99),"-","")

 

[Peter_SSs]

Now i am facing another issue.

How can i get the result for the b/m.

123-4567/123-4568 => 61234567/61234568
1234-5678/877-9854 => 12345678/68779854

Do these 'double-bungers' always have a "-" in the left part and the right part like these two examples or might there be cases like these for example?
12345678/123-4567
12345678/12345678

I've assumed a variety is possible.

For a formula solution, my suggestion is
=RIGHT(SUBSTITUTE(6&LEFT(A2,FIND("/",A2)-1),"-",""),8)&"/"&RIGHT(SUBSTITUTE(REPLACE(A2,1,FIND("/",A2),6),"-",""),8)


For a udf approach, I would avoid all the looping and use of WorksheetFunctions when suitable vba functions exist, since both those things will slow the code. CyFoo, you might like to investigate the vba
- 'Replace' function to use in place of the worksheetfunction 'Substitute', and
- 'InStr' function in place of the worksheetfunction 'Find'

<font face=Courier New><br><SPAN style="color:#00007F">Function</SPAN> FixIt(s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> B<br>    <br>    B = Split(Replace(s, "-", ""), "/")<br>    B(0) = Right(6 & B(0), 8)<br>    B(1) = Right(6 & B(1), 8)<br>    FixIt = Join(B, "/")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br></FONT>

Use in the worksheet like:
=FixIt(A2)

 

[CyFoo]

@Peter: Thanks for sharing. Yeah, I noticed that the substitute() finds and replaces the old text with the new text without needing to loop, after I made that post. Thanks for pointing that out.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>

Do While InStr(1, text, "-") > 0
…<o></o>
s = WorksheetFunction.Find("/", text) <o></o>

Funny that I have used InStr in the loop but not in finding the position of the "/" :? Thanks for the tips too and I will take a look into the Replace(). I just thought it would be more familiar for Vengeancez to see Excel codes and know what just happened.<o></o>
<o></o>

CyFoo, you might like to investigate the vba
- 'Replace' function to use in place of the worksheetfunction 'Substitute', and
- 'InStr' function in place of the worksheetfunction 'Find'<o></o>

Is it because these are VBA functions instead of Excel functions? Will it be faster calling native VB functions in comparison to Excel functions? Kindly comment<o></o>
<o></o>
Great codes BTW :D both the methods. And Split() & Join() are the best<o></o>
<o></o>
Many thanks! Learn plenty this morning<o></o>

 

[bebo021999]

Thanks for the formula, it works well.

Now i am facing another issue.

How can i get the result for the b/m.

123-4567/123-4568 => 61234567/61234568
1234-5678/877-9854 => 12345678/68779854

Try this:

=RIGHT("6"&SUBSTITUTE(LEFT(A1,FIND("/",A1)-1),"-",""),8)&"/"&RIGHT("6"&SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("/",A1)),"-",""),8)

 

[Peter_SSs]

Is it because these are VBA functions instead of Excel functions? Will it be faster calling native VB functions in comparison to Excel functions?

Yes, generally significantly faster.