Extracting String from a data with string

[rom]

Hey all , need urgent help.I am stuck with a VBA project because of this problem.I got a set of data which is seperated by symbol "/", the code i am trying to build is

if cell value is for eg ABC0604082/00797983 then result should be 00797983

if cell value is ABC0604082/ then result should be ABC0604082


I tried to do text to columns but the result doesn't have leading 0s, which means 00797983 is showing as 797983 & also it'll not belp me with my 2nd condition.


Please help

 

[Hui]

Rom

[pre]

Sub MyTxt()
Txt = "ABC0604082/00797983" 'then result should be 00797983
'Txt = "ABC0604082/" 'should be ABC0604082/

If Right(Txt, 1) = "/" Then
MsgBox Left(Txt, Len(Txt) - 1)
Else
MsgBox Right(Txt, Len(Txt) - InStr(1, Txt, "/"))
End If
End Sub

[/pre]

 

[keymaster]

Hi Rom... Use this.


Assuming the value is in A1, write

=text(if(right(A1,1)="/",left(A1,len(A1)-1),mid(A1,find("/",A1)+1,999)),"@")


This should extract either left or right portion while retaining leading zeros.

 

[shrivallabha]

Hi Rom,


One more way assuming your data is in column A starting from Row 1.

[pre]

Public Sub SplitItUp()
Dim varSplit As Variant
For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
varSplit = Split(Range("A" & i).Value, "/")
If Len(varSplit(UBound(varSplit))) Then
MsgBox varSplit(UBound(varSplit))
Else
MsgBox varSplit(LBound(varSplit))
End If
Next i
End Sub

[/pre]

 

[Faseeh]

Hi all,


we may try this as well:


=If(Iserror(Find("/",A1))=True,A1,Trim(Mid(A1,Find("/",A1),999)))

 

[Debraj]

Hi Rom,


I have a little bit longer trick.. No VBA, No Function.. only Recording..


* Select Column.

* Text to Column.

* Delimeter > "/"

* Next > change both separated columns to TEXT.

* Now you have Two Column (ABC0604082 & (Blank or 00797983))

* Copy Column B.

* Paste Special in Column A, Skip Blanks..

* delete Column B.


Useful.. only in case of long list of Data, and want No Drag & Drop..


Regards,

Deb

 

[shrivallabha]

Hi Deb,


Go ahead and post the recorded code!

 

[Debraj]

@ Shri..


Ha ha ha.. but in case of multiple "/" you will pass due to InStrRev.. and middle data will be overlooked..


unlike all other post... Which I think better for OP.


Regards,

Deb