find p- and split before it to next cell

[shrinivasmj]

i need a vba code

its in cell b1 split to cell c2

find p- and split before it to next cell

CLI-65389 CLI-OvQ-Jmn_65389 P-805183301131393 RMLCEOSHGHMVKTNB Western Mutual Insurance Group B6ZT9YJ7 Protection Plans LI2542168 475 2 Year YVONNE M NELSON 460 W SCHULTE RD ATLANTA CA 90136 (773) 224-9209 3

 

[jproffer]

So you want this split out..."CLI-65389 CLI-OvQ-Jmn_65389" ?

If so:

=TRIM(LEFT(B1,FIND("P-",B1)-1))

It will work with or without the TRIM around it, but just for good measure...

 

[shrinivasmj]

i need a vba code as there are 100 of data

 

[jproffer]

You could just drag that formula down, but....Ok, this is totally untested because I'm at home and no excel t test on, but I'll give it a shot.

Dim MyRange as Range
Dim Cell as Range

MyRange = Range("[B][COLOR=#ff0000]B1:B100[/COLOR][/B]")

For Each Cell in MyRange
    Cell.Offset(1,1).Value = Left(Cell.Value,Instr(Cell.Value,"P-"))
Next Cell

Change what I have in red to the Range your data is in.

This will put the left characters until it finds "P-" one cell to the right and one cell down from the original data (you said to put B1 into C2...not my idea).

PLEASE, PLEASEEEEEE test it on a COPY of your workbook, as it will not be able to be undone if I got something wrong. As I said, it is completely untested and written totally from memory.

You may have to change this:

Cell.Offset(1,1).Value = Left(Cell.Value,Instr(Cell.Value,"P-"))

to this:

Cell.Offset(1,1).Value = Left(Cell.Value,Instr(Cell.Value,"P-")[B][COLOR=#0000cd]-1[/COLOR][/B])

or some other number to get what you want.

 

[shrinivasmj]

not able to run vba cose its stoping in myrange

 

[jproffer]

Sorry. Put Set in front of it.

Set MyRange = Range("B1:B100")

 

[Peter_SSs]

If your question has not been resolved, could you please give 4-5 samples of data AND the expected results?
Also, please specify exactly where the data is and where then results should go.

 

[shrinivasmj]

this is the data below

in cell B:B
AMI-77561 AMI-OvQ-Jmn_77561 P-751502213743641 IIJH7NQSP58AGIZB
CLI-77561 CLI-OvQ-Jmn_77561 P-320497742131143 YAZZXGW02B8B40YA
HI-77561 HI-OvQ-Jmn_77561 P-210686976842370 CRDN69JJ4HH4EM7U

OUT PUT I NEED

CELL B;B---AMI-77561 AMI-OvQ-Jmn_77561 CELL C ---P-751502213743641 IIJH7NQSP58AGIZB
CELL B;B---CLI-77561 CLI-OvQ-Jmn_77561 CELL C --- P-320497742131143 YAZZXGW02B8B40YA
CELL B;B---HI-77561 HI-OvQ-Jmn_77561 CELL C --- P-210686976842370 CRDN69JJ4HH4EM7U

 

[Peter_SSs]

Try

Sub Split_Text()
  With Range("B1", Range("B" & Rows.Count).End(xlUp))
    .Offset(, 1).Value = Evaluate(Replace("if(row(#),replace(#,1,search("" P-"",#&"" P-""),""""),"""")", "#", .Address))
    .Value = Evaluate(Replace("if(row(#),left(#,search("" P-"",#&"" P-"")-1),"""")", "#", .Address))
  End With
End Sub

 

[shrinivasmj]

Try

Sub Split_Text()
  With Range("B1", Range("B" & Rows.Count).End(xlUp))
    .Offset(, 1).Value = Evaluate(Replace("if(row(#),replace(#,1,search("" P-"",#&"" P-""),""""),"""")", "#", .Address))
    .Value = Evaluate(Replace("if(row(#),left(#,search("" P-"",#&"" P-"")-1),"""")", "#", .Address))
  End With
End Sub

in cell c:c its showing value