move data to other column based on a condition

[duwell81]

i have a spreadsheet that has data in column a that varies in format with some of the data. I am trying to retreive all data that is after the right parenthesis in some of the rows in column A. I want to extract that data and put it in another column such as I or K one row down from the row its located in. I would like to be able to change the column i want the data in before the code is run. An example is below.

Column A

R1 pci10(456392)lc41
R2 pci11(12345465)hp22
R3 pci56(43455)lc21
R4 hello34(43434)
R5 uouavib3040
R6 pci85(234A009)h30
R7 aassdlf0983
and so on......


After
Column K or or any other column I would choose to have my data put in after the parenthesis.

Column K
R1
R2 lc41
R3 hp22
R4 lc21
R5
R6
R7 hp30
R6
R7
and so on.....

 

[villy]

Try this...

Sub transfer()
lRow = Cells(Rows.Count, "A").End(xlUp).Row
For chk = 1 To lRow         'Check for the first non-empty cell in A
    If Range("A" & chk) <> Empty Then
        Exit For
    End If
Next
Col = InputBox("In which column you want your data?", "Column")
For i = chk To lRow
Set A_Rng = Range("A" & i)
With WorksheetFunction.Application
    If Not IsError(.Search(")", A_Rng)) = True Then
        dat = Right(A_Rng, Len(A_Rng) - .Search(")", A_Rng))
        Range(Col & i + 1).Value = dat
    End If
End With
Next
End Sub

 

[Marcelo Branco]

Hi,

Assuming your data begin at row 1, maybe

type R1 in K1

K2
=TRIM(LEFT(A2,SEARCH(" ",A2))&RIGHT(A1,LEN(A1)-LOOKUP(9.99E+307,CHOOSE({1,2},LEN(A1),SEARCH(")",A1)))))

copy down

HTH

M.

 

[Biz]

Hi,

Assuming your data begin at row 1, maybe

type R1 in K1

K2
=TRIM(LEFT(A2,SEARCH(" ",A2))&RIGHT(A1,LEN(A1)-LOOKUP(9.99E+307,CHOOSE({1,2},LEN(A1),SEARCH(")",A1)))))

copy down

HTH

M.

Nice formula

Biz

 

[Marcelo Branco]

Nice formula

Biz

Biz,

Tks very much

M.

 

[villy]

Hi,

9.99E+307

I want to do something like this n my formulas, What does this mean if you don't mind?What if I want to include not only number but alphanumeric?
Thanks

 

[Yahya]

Villy 9.99999999999999E+307 the big number in Excel
see this link
http://www.mrexcel.com/forum/showthread.php?t=102091

 

[Marcelo Branco]

Villy 9.99999999999999E+307 the big number in Excel
see this link
http://www.mrexcel.com/forum/showthread.php?t=102091

Yeah, this is my source also !

M.

 

[villy]

what if use as alphanumeric what would be the constant value to be used?
For example in the begining thread instead of just numbers inside the parenthesis there are alpha also, so what would be the formula?
Thanks

 

[Marcelo Branco]

I want to do something like this n my formulas, What does this mean if you don't mind?What if I want to include not only number but alphanumeric?
Thanks

If you have a mixed range (named MyRange in my example below), Numbers and Text, like

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64 align=right>45</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>a</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>b</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>99</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>120</TD></TR></TBODY></TABLE>

or

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64 align=right>45</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>a</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>b</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>99</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>c</TD></TR></TBODY></TABLE>

to get the last cell you need the MAX between

MATCH(9.99E+307,MyRange)

and

MATCH(REPT("z",255),MyRange)

M.