number issue

[ahhhmed]

suppose I have a two digit number, eg 86 in A2, How can I insert a symbol between the two digits in B2 . the number will be as follows: 8/6

 

[Hui]

You can do a Search Replace or Ctrl H

Search for 8

Replace with '8/


If you don't include the ' Excel will interpret 8/6 as a date

 

[ahhhmed]

Thanks Hui,

I want this to be automatically done, because it is a step in a process.

 

[GCExcel]

Hi ahhhmed,


Assuming that your numbers don't always start with "8", you could use this macro.

(the macro will add a "/" before the last character)

[pre]

Dim rg As Range
Dim c As Range

Set rg = [A1:A5]  'range containing your values
For Each c In rg
c = "'" & Left(c, 1) & "/" & Right(c, 1)     'for 2 digits numbers
' or
' c = "'" & Left(c, Len(c) - 1) & "/" & Right(c, 1)   ' for x digits numbers
Next c

[/pre]

 

[ahhhmed]

Thanks,

Can I do this with a formula instead of a macro?

 

[Hui]

In B2 put

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

 

[ahhhmed]

Thanks a lot. This is what I'm looking for.