PDA

View Full Version : Solved: Putting a slash between numbers and text



YasserKhalil
07-27-2010, 01:59 PM
Simply I have some data which is a mixture of numbers and text
I want to put a slash between numbers and text
1245624Yasser should be like that 1245624/Yasser

Bob Phillips
07-27-2010, 02:58 PM
Put this array formula in B2

=MIN(IF(ISERROR(1*(MID(A2,ROW(INDIRECT("A1:A"&LEN(A2))),1))),
ROW(INDIRECT("A1:A"&LEN(A2))),255))-1

and this formula in C2

=LEFT(A2,B2)&"/"&RIGHT(A2,LEN(A2)-B2)

mdmackillop
07-27-2010, 03:06 PM
Dim Num As String
Dim Cel As Range
For Each Cel In Selection
Num = Val(Cel)
Cel.Value = Num & "/" & Right(Cel, Len(Cel) - Len(Num))
Next

YasserKhalil
07-27-2010, 03:26 PM
Thanks for these great solutions
What if the text is the first entry ,i.e Yasser56421311?

Bob Phillips
07-27-2010, 03:54 PM
B2: =MAX(IF(ISNUMBER(SEARCH({0,1,2,3,4,5,6,7,8,9},A2)),SEARCH({0,1,2,3,4,5,6,7, 8,9},A2)))

C2: =LEFT(A2,B2)&"/"&MID(A2,B2+1,99)

YasserKhalil
07-27-2010, 04:08 PM
Sorry Mr. xld
Your last post doesn't work correctly

Simon Lloyd
07-27-2010, 04:24 PM
Bob, i was messing around with this and although it works i'm a little confused, when i used ISNUMBER it equates to false, so i'm assuming that ISNUMBER(SEARCH({0,1,2,3,4,5,6,7,8,9},A2)) will equate to false........maybe it's late and i'll see sense later tomorrow when i get up :)

mdmackillop
07-28-2010, 12:03 AM
Dim Num As String
Dim Cel As Range
Dim i As Long
Dim Test1 As Long, Test2 As Long
For Each Cel In Selection
For i = 1 To Len(Cel) - 1
Test1 = 60 - Asc(Mid(Cel, i, 1))
Test1 = Test1 / Abs(Test1)
Test2 = 60 - Asc(Mid(Cel, i + 1, 1))
Test2 = Test2 / Abs(Test2)
If Test1 <> Test2 Then Exit For
Next
Cel.Value = Left(Cel, i) & "/" & Right(Cel, Len(Cel) - i)
Next

Bob Phillips
07-28-2010, 01:14 AM
Sorry Mr. xld
Your last post doesn't work correctly

What do you mean by doesn't work Yasser. I tested it and I can assure you it does work.

Did you array-eneter the first formula?

Bob Phillips
07-28-2010, 01:18 AM
Bob, i was messing around with this and although it works i'm a little confused, when i used ISNUMBER it equates to false, so i'm assuming that ISNUMBER(SEARCH({0,1,2,3,4,5,6,7,8,9},A2)) will equate to false........maybe it's late and i'll see sense later tomorrow when i get up :)

Simon, it evaluates to an array of TRUE/FALSE.

I use ISNUMBER because SEARCH will return a number of #VALUE, so I need to remove the #VALUE to stop MAX from choking.

By using the IF test, the final array is an array of index positions and FALSE values, and MAX will process FALSE quite happily.

YasserKhalil
07-28-2010, 01:50 AM
Mr. mdmackillop
Thanks a lot for your excellent code
It works fine

But I still need the solution using formulas

Mr. xld
Yes, I pressed Ctrl+Shift+Enter for the first formula
and tried it without doing that .. In both tries, it doesn't work
I tried more than ten times and every time it doesn't work
Generally I'll attach a file to see what's wrong

Bob Phillips
07-28-2010, 02:34 AM
Looks as though I had it backwards

B2: =MIN(IF(ISNUMBER(SEARCH({0,1,2,3,4,5,6,7,8,9},A2)),SEARCH({0,1,2,3,4,5,6,7, 8,9},A2)))

C2: =LEFT(A2,B2-1)&"/"&MID(A2,B2,99)

YasserKhalil
07-28-2010, 05:50 AM
That's it
Now it works perfect Mr. xld
Thanks a lot

Simon Lloyd
07-28-2010, 07:40 AM
Simon, it evaluates to an array of TRUE/FALSE.

I use ISNUMBER because SEARCH will return a number of #VALUE, so I need to remove the #VALUE to stop MAX from choking.

By using the IF test, the final array is an array of index positions and FALSE values, and MAX will process FALSE quite happily.Thanks for clearing that up Bob :)