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.

 

[Rick Rothstein]

You didn't say where exactly you wanted the 6 added at, so I assumed on the end. Give this formula a try...

=A1&LEFT(6,LEN(SUBSTITUTE(A1,"-",""))=7)

 

[Vengeancez]

Oh, i want to put it at the front. Sorry, forget to say.

 

[CyFoo]

Try:

=IF(LEN(SUBSTITUTE(A1,"-",""))=7,"6"&SUBSTITUTE(A1,"-",""),SUBSTITUTE(A1,"-",""))

 

[Vengeancez]

I've tired your formula, somehow i am getting this error, Err:508. Sorry, but i'm a newbie, hope i am not annoying you.

 

[CyFoo]

Tried this in Excel2007:
=IF(LEN(SUBSTITUTE(A1,"-",""))=7,"6"&SUBSTITUTE(A1,"-",""),SUBSTITUTE(A1,"-",""))

Results:
123-4567 => 61234567
1234-5678 => 12345678
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64></COLGROUP><TBODY></TBODY></TABLE>

 

[Vengeancez]

I've tired your formula, somehow i am getting this error, Err:508. Sorry, but i'm a newbie, hope i am not annoying you.

I realise, it is because i am using Calc thats why i am facing the Err:508. it works perfectly fine in excel. Thanks for the help guys

 

[Vengeancez]

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

 

[CyFoo]

not sure if i left anything out, but try:

1. alt-11

2. insert module

3. paste the following

Function udf(text)
Dim l As String, r As String, s As Long
Do While InStr(1, text, "-") > 0
text = WorksheetFunction.Substitute(text, "-", "")
Loop
s = WorksheetFunction.Find("/", text)
l = left(text, s - 1)
r = right(text, Len(text) - s)
If Len(l) = 7 Then l = "6" & l
If Len(r) = 7 Then r = "6" & r
udf = l & "/" & r
End Function

4. in cell a2:

=udf(a1)

 

[Vengeancez]

not sure if i left anything out, but try:

1. alt-11

2. insert module

3. paste the following



4. in cell a2:

Wow, this is a bit high level for me, after inserting the module and pasting of the a/m, i have no idea what to press next.