Find a / in a string

V

vavs

Well-known Member
Joined
Jul 2, 2004
Messages
514
I need to review a list of approximately 5000 cells. The cell contains either a number format something like .2345 or a number with a slash .071/.068

What I want to do seems simple but I don't know how to do it effectively:

If the cell does not contain a /, then I want to put the cell value into two cells d2 and e2.
If the number does contain a / , then I want to put the number to the left of the slash into the d2 cell and the number to the right of the slash into the e2 cell.

I have tried to do a select case statement to have it step through each scenario, but I cannot figure out how to find the / in the first case. After the slash is found, I am guessing the vba script should be similar to excel: LEFT($C2,FIND("/",$C2,1)-1) where C2 contains the original string.

I also have cases for when the cell contains "GA". Those are more complex because I have to step through what number is in front of the GA. Not difficult but definitely time consuming.

Thanks in advance for your help.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Do you really need vba?
In cell D2:
=IF(ISERROR(FIND("/",C2)),C2,LEFT(C2,FIND("/",C2)-1))
in cell E2:
=IF(ISERROR(FIND("/",C2)),C2,MID(C2,FIND("/",C2)+1,100))

After copying down, do a copy/paste-special values if necessary.
 
Upvote 0
You might also just use Text To Columns...

Highlight the column
Data - Text To Columns
Deliminated
OTHER, put in the /
Finish.

However, this will not do the first thing, put the value in 2 cells when it doesn't contain /.
 
Upvote 0
Hello

Try the code below, I am a novice SO PLEASE TEST FIRST.
It a assumes that the data starts in A2, and sheet with the data is the activesheet.
If you need to change the range I have added coments.


Code: 
Sub vavs()
LR = Range("A" & Rows.Count).End(xlUp).Row 'Start column = A
For i = 2 To LR 'Start row = 2
    With Range("A" & i) 'Start column = A
        X = Split(.Value, "/")
        If UBound(X) = 1 Then
                .Cells(, 4).Value = Trim(X(0))
                .Cells(, 5).Value = Trim(X(1))
                Else
            .Cells(, 4).Resize(2, 2).Value = .Value
        End If
    End With
Next i
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Matching a partial string to a list of longer strings
Replies
1
Views
186
duggie33
D
P
  • Solved
VBA Find first empty cell in row
2
Replies
10
Views
2K
MARK858
MARK858
G
  • Solved
Match Number, then Find a string and extract the data between the brackets.
Replies
7
Views
438
Peter_SSs
Peter_SSs
R
  • Question
How to use IS NUMBER Search and IF AND together
Replies
1
Views
237
Joe4
Joe4
K
  • Question
Conditional Formatting cells that contain matching strings of text
Replies
1
Views
805
ksjohnson86
K

Forum statistics

Threads
1,214,596
Messages
6,120,438
Members
448,966
Latest member
DannyC96

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top