Manipulating Text

[RAF1112]

Here is a tough one...

I have the following in a few cells (and others like it) in a cell:

ACC300-245
BD394-231

Can I manipulte the data, using a formula, to get:

ACC-300
BD-394

Any suggestions?

Thanks for your help

 

[XLENT]

are the numbers always 3 digits? If not, I can't think of a way to do it without using vba.

 

[RAF1112]

Yes, the numbers are always in 3-digit sets

 

[Richie(UK)]

Hi,

There is probably a neater way of doing this (especially if Aladin sees this thread :wink: ), but in the meantime try this:

If the text is in A1,
=LEFT(A1,LEN(A1)-7)&"-"&MID(A1,LEN(A1)-6,3)

HTH

 

[geolefty]

here is a quick thought.

there is probably a much more graceful way to do this and this only works if these are the only 2 number types but try this.

put the formula one cell to the right of your data.


=IF(LEN(C8)=10,CONCATENATE(LEFT(C8,3),"-",CONCATENATE(MID(C8,4,3))),CONCATENATE(LEFT(C8,2),"-",CONCATENATE(MID(C8,3,3))))

 

[Aladin Akyurek]

Yes, the numbers are always in 3-digit sets

Using some functions from the morefunc.xlladd-in...

Book4
	A	B	C	D
1	ACC300-245	ACC-300
2	BD394-231	BD-394
3	x450-908	x-450
4	cvbxd879-879	cvbxd-879
5
Sheet1

The formula in B1 is...

=REPLACE(SETV(WMID(A1,1,1,"-")),LEN(GETV())-2,0,"-")

 

[IML]

Not as robust, but just for fun
=LEFT(A1,3-(ISNUMBER(MID(A1,3,1)+0)))&TEXT(MID(A1,FIND("-",A1)-3,3),"-000")

seems to work with your sample data.

 

[Egress1]

FWIW

In VBA

Sub manipulate_data()
[A1].Select
Do While ActiveCell.Value <> 0
ORIGSTRG = ActiveCell.Value
For Y = 1 To Len(ORIGSTRG)
If IsNumeric(Mid(ORIGSTRG, Y, 1)) Then
NewStrg = Left(ORIGSTRG, Y - 1) & "-" & Right(ORIGSTRG, Len(ORIGSTRG) - Y)
ActiveCell.Value = NewStrg
Exit For
End If
Next Y
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

 

[Yogi Anand]

Hi RAF1112:

You may also want to try ...

y030515h1.xls
	A	B	C	D
1	ACC300-245	ACC-300
2	BD394-231	BD-394
3	x450-908	x-450
4	cvbxd879-879	cvbxd-879
Sheet3

I hope this helps.

 

[Mark W.]

Since everyone else is doing it...

=LEFT(REPLACE(A1,FIND("-",A1)-3,,"-"),FIND("-",A1))