Move contents of cell up and over one

[34sweetness]

I have a list of data that is in one column that has 100s of rows.
I need to make 2 columns by moving the contents of one cell up and over one column so that it is to the right of the cell that used to be "above" it.

I need to do this all the way down the first column, but only if the cell contents that needs to move up and right starts with "DOC".

Is there an easy way to do this?

 

[CWatts]

Assuming your list starts in A3, in cell B2, you can use the following formula and copy it down the column:

=IF(LEFT(A3,3)="DOC",A3,"")

That will at least extract the cells you want.

If your definition of "move" is to put them in the second column and delete them from the first, then you'd be looking at some VBA code which someone else would have to help as I'm not that good at it.

 

[Peter_SSs]

You can probably complete the job manually if you want.

With the set-up described by CWatts and that formula applied as suggested, then do these steps (test in a copy of your workbook)

1. Select column B by clicking its heading label

2. Copy the Paste Special (Values only) - post back with your Excel version if you don't know how.

3. Select Column A by clicking its heading label

4. Edit|Replace...|Find what: DOC*|Replace with: leave blank|Replace All|OK|Close

 

[Nalani]

And another approach If the OP comes back and says "What if DOC is at the end or the middle?"

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-WEIGHT: bold">Data</TD><TD style="FONT-WEIGHT: bold">Results</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD></TD><TD style="COLOR: #ff0000">DOC123</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>DOC123</TD><TD style="COLOR: #ff0000">DOC456</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>DOC456</TD><TD style="COLOR: #ff0000">DOCtest</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>DOCtest</TD><TD style="COLOR: #ff0000"></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>ABC123</TD><TD style="COLOR: #ff0000"></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>ABCtest</TD><TD style="COLOR: #ff0000">123DOC</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>123DOC</TD><TD style="COLOR: #ff0000"></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>test 456</TD><TD style="COLOR: #ff0000">12DOC45</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>12DOC45</TD><TD style="COLOR: #ff0000"></TD></TR></TBODY></TABLE>


Formula in B2 copied down is =IF(ISERROR(FIND("DOC",A3,1)),"",A3)

Then to do what Peter suggested:

Under Find What: enter *DOC*

 

[Zeus2008]

And another approach If the OP comes back and says "What if DOC is at the end or the middle?"

Sheet1

	A	B
Data	Results
	DOC123
DOC123	DOC456
DOC456	DOCtest
DOCtest
ABC123
ABCtest	123DOC
123DOC
test 456	12DOC45
12DOC45

<tbody>
[TD="bgcolor: #CACACA, align: center"]1
[/TD]

[TD="bgcolor: #CACACA, align: center"]2
[/TD]

[TD="bgcolor: #CACACA, align: center"]3
[/TD]

[TD="bgcolor: #CACACA, align: center"]4
[/TD]

[TD="bgcolor: #CACACA, align: center"]5
[/TD]

[TD="bgcolor: #CACACA, align: center"]6
[/TD]

[TD="bgcolor: #CACACA, align: center"]7
[/TD]

[TD="bgcolor: #CACACA, align: center"]8
[/TD]

[TD="bgcolor: #CACACA, align: center"]9
[/TD]

[TD="bgcolor: #CACACA, align: center"]10
[/TD]

</tbody>

Formula in B2 copied down is =IF(ISERROR(FIND("DOC",A3,1)),"",A3)

Then to do what Peter suggested:

Under Find What: enter *DOC*

-----------------------------------------------------------------------------------------------
I have the same situation but instead of the item starting with DOC or containing DOC, I have a phone number with and without parenthesis (###) ###-#### or ###-###-#### so how could I do the same, move over to the right and up one cell?

 

[Zeus2008]

All is good, I figured it out

 

[CWatts]

-----------------------------------------------------------------------------------------------
I have the same situation but instead of the item starting with DOC or containing DOC, I have a phone number with and without parenthesis (###) ###-#### or ###-###-#### so how could I do the same, move over to the right and up one cell?

Dang this thread is old. I give you credit for using the search functionality.

Try this formula. It will strip out (, ), -, and <space> <space>as well as validate that there are 10 digits. It's a bit cumbersome and clunky (and would be better served in a UDF or NamedRange, but give it a shot.

=IFERROR(IF(AND(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"(",""),")",""),"-","")," ","")*1>=1000000000,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"(",""),")",""),"-","")," ","")*1<9999999999),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"(",""),")",""),"-","")," ",""),""),"Error")

</space>

 

[Zeus2008]

Dang this thread is old. I give you credit for using the search functionality.

Try this formula. It will strip out (, ), -, and <space> <space>as well as validate that there are 10 digits. It's a bit cumbersome and clunky (and would be better served in a UDF or NamedRange, but give it a shot.

=IFERROR(IF(AND(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"(",""),")",""),"-","")," ","")*1>=1000000000,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"(",""),")",""),"-","")," ","")*1<9999999999),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"(",""),")",""),"-","")," ",""),""),"Error")

</space>

Thanks for taking the time to reply back - I actually took the original formula and just adjusted it a bit as follows: =IF(ISERROR(FIND("",A3,1)),"",A3) - this works just as fine and less cumbersome - Cheers!
</space>

 

[Peter_SSs]

Thanks for taking the time to reply back - I actually took the original formula and just adjusted it a bit as follows: =IF(ISERROR(FIND("",A3,1)),"",A3) - this works just as fine and less cumbersome - Cheers!

Welcome to the MrExcel board!

This is also less cumbersome. Does it do what you want?
If not, can you provide some sample data and further explanation about in what circumstances it fails?

=IF(A3="","",A3)

 

[Zeus2008]

Welcome to the MrExcel board!

This is also less cumbersome. Does it do what you want?
If not, can you provide some sample data and further explanation about in what circumstances it fails?

=IF(A3="","",A3)

----------------------------------------------------------------------------------
You're right, that is much easier - thank you!