formula for, if excel cell range ( 3 cells in row) contains any number copy entire cell to another cell

P

PeterNYC212

New Member
Joined
Mar 16, 2023
Messages
7
Office Version
  1. 365
Platform
  1. Windows
I have a spread sheet with up to 4 columns of names, then the last cell is the actual address. But I am looking to move the adress cell its own column. It could like like any of the examples below. Looking to move the address field into a new single column. Is there an recommended formula for this. My thought was if the cell started with or contained any number value I would copy that entire cell into a new cell, and create a single column with the addess. I appreciate any help on this. Thank you
JEANETTE P ESPOSITO TRJOHN L ESPOSITO LIV TRUSTJEANETTE P ESPOSITO LIV TRUST875 WHISKEY CREEK DR
SARA N LEIDALMARGRET N FLORIO2575 MAIDENS ROAD
DONA LEIDER1756 LUDLOW RD
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
try this;
mr excel questions 15.xlsm
ABCDE
1JEANETTE P ESPOSITO TRJOHN L ESPOSITO LIV TRUSTJEANETTE P ESPOSITO LIV TRUST875 WHISKEY CREEK DR875 WHISKEY CREEK DR
2SARA N LEIDALMARGRET N FLORIO2575 MAIDENS ROAD2575 MAIDENS ROAD
3DONA LEIDER1756 LUDLOW RD1756 LUDLOW RD
Sheet9
Cell Formulas
RangeFormula
E1:E3E1=INDEX(A1:D1,1,4-SUM((--(A1:D1=""))))
 
Upvote 0
Another option
Fluff.xlsm
ABCDE
1
2JEANETTE P ESPOSITO TRJOHN L ESPOSITO LIV TRUSTJEANETTE P ESPOSITO LIV TRUST875 WHISKEY CREEK DR875 WHISKEY CREEK DR
3SARA N LEIDALMARGRET N FLORIO2575 MAIDENS ROAD2575 MAIDENS ROAD
4DONA LEIDER1756 LUDLOW RD1756 LUDLOW RD
Summary
Cell Formulas
RangeFormula
E2:E4E2=LOOKUP(2,1/(A2:D2<>""),A2:D2)
 
Upvote 0
Solution
and another
Book1
ABCDEF
1JEANETTE P ESPOSITO TRJOHN L ESPOSITO LIV TRUSTJEANETTE P ESPOSITO LIV TRUST875 WHISKEY CREEK DR875 WHISKEY CREEK DR
2SARA N LEIDALMARGRET N FLORIO2575 MAIDENS ROAD2575 MAIDENS ROAD
3DONA LEIDER1756 LUDLOW RD1756 LUDLOW RD
Sheet1
Cell Formulas
RangeFormula
F1:F3F1=INDIRECT(ADDRESS(ROW(),MATCH("*1*",A1:D1,-1)))
 
Upvote 0
awoohaw said:
=INDEX(A1:D1,1,4-SUM((--(A1:D1=""))))
Click to expand...
Its doing some of them correct. I tried but am getting this. Maybe its best to just copy the last cell with a value in column E. Ill repost the another sample below. Move from Name1-Name5, into the new address column. Only the last column with data has the address I am looking to put in a single column.

SHARON D DELAWDER LIV TRUST		340 SEABREEZE DR20 E DUNDEE CIRCLE
DAVID A=& KAREN MARIE WURZRONALD C=& COLLEEN WURZ346 SEABREEZE DR320 SEABREEZE DR
KAREN S BLAKE QPR TRUST356 SEABREEZE DR326 SEABREEZE DR
% WALTER W BETTINGERPO BOX 1308475 GATE HOUSE CT
MARY S COLLINS162 SOUTH BEACH DR188 S BEACH DR
KELLETT LAND TRUSTUTD 5/13/0289 HILL COUNTRY DR152 SOUTH BEACH DR
LINDA SUE AMEND7635 MILLIKIN ROAD946 SAND DUNE DR
PO BOX 611 ELM CIRCLE321 SEABREEZE DR
HEATHER DRENNAN LIV TRUST320 WILD ORCHARD LN331 SEABREEZE DR
CARMEL A SHIELDS409 HEATH ST320 WILD ORCHARD LN
NAME1NAME2NAME3NAME4NAME5move street address here
E P=& D F ROCHE MARCO IS TRUSTUTD 10/10/98320 SEABREEZE DR
MYRNA M MOONAN-DIAZ TRDIAZ FAMILY TRUSTUTD 10/1/101289 FRUITLAND AVE
RICHARD ZIC LIVING TRUSTZIC TR, MAURA=& RICHARDMAURA ZIC LIVING TRUST6760 N LEOTI AVE
SHARON D DELAWDER LIV TRUST340 SEABREEZE DR
BOYCE, THOMAS HJOAN SCHADE-BOYCEELIZABETH BOYCE TRKEVIN C O'NEILL TR88 MADAGASCAR CT
 
Upvote 0
Did you try the formula I suggested?
 
Upvote 0
Fluff said:
Did you try the formula I suggested?
Click to expand...
I just tried it and I got this.
NAME1NAME2NAME3NAME4NAME5move street address here
E P=& D F ROCHE MARCO IS TRUSTUTD 10/10/98320 SEABREEZE DR320 SEABREEZE DR
MYRNA M MOONAN-DIAZ TRDIAZ FAMILY TRUSTUTD 10/1/101289 FRUITLAND AVE320 SEABREEZE DR
RICHARD ZIC LIVING TRUSTZIC TR, MAURA=& RICHARDMAURA ZIC LIVING TRUST6760 N LEOTI AVE320 SEABREEZE DR
SHARON D DELAWDER LIV TRUST340 SEABREEZE DR320 SEABREEZE DR
BOYCE, THOMAS HJOAN SCHADE-BOYCEELIZABETH BOYCE TRKEVIN C O'NEILL TR88 MADAGASCAR CT320 SEABREEZE DR
 
Upvote 0
That is not what I get.
Fluff.xlsm
ABCDEF
1NAME1NAME2NAME3NAME4NAME5move street address here
2E P=& D F ROCHE MARCO IS TRUSTUTD 10/10/98320 SEABREEZE DR320 SEABREEZE DR
3MYRNA M MOONAN-DIAZ TRDIAZ FAMILY TRUSTUTD 10/1/101289 FRUITLAND AVE1289 FRUITLAND AVE
4RICHARD ZIC LIVING TRUSTZIC TR, MAURA=& RICHARDMAURA ZIC LIVING TRUST6760 N LEOTI AVE6760 N LEOTI AVE
5SHARON D DELAWDER LIV TRUST340 SEABREEZE DR340 SEABREEZE DR
6BOYCE, THOMAS HJOAN SCHADE-BOYCEELIZABETH BOYCE TRKEVIN C O'NEILL TR88 MADAGASCAR CT88 MADAGASCAR CT
Summary
Cell Formulas
RangeFormula
F2:F6F2=LOOKUP(2,1/(A2:E2<>""),A2:E2)


Can you post your data using the XL2BB add-in so that we can see what your formula is doing.
 
Upvote 0
Fluff said:
That is not what I get.
Fluff.xlsm
ABCDEF
1NAME1NAME2NAME3NAME4NAME5move street address here
2E P=& D F ROCHE MARCO IS TRUSTUTD 10/10/98320 SEABREEZE DR320 SEABREEZE DR
3MYRNA M MOONAN-DIAZ TRDIAZ FAMILY TRUSTUTD 10/1/101289 FRUITLAND AVE1289 FRUITLAND AVE
4RICHARD ZIC LIVING TRUSTZIC TR, MAURA=& RICHARDMAURA ZIC LIVING TRUST6760 N LEOTI AVE6760 N LEOTI AVE
5SHARON D DELAWDER LIV TRUST340 SEABREEZE DR340 SEABREEZE DR
6BOYCE, THOMAS HJOAN SCHADE-BOYCEELIZABETH BOYCE TRKEVIN C O'NEILL TR88 MADAGASCAR CT88 MADAGASCAR CT
Summary
Cell Formulas
RangeFormula
F2:F6F2=LOOKUP(2,1/(A2:E2<>""),A2:E2)


Can you post your data using the XL2BB add-in so that we can see what your formula is doing.
Click to expand...
Name sample file.xlsx
ABCDEF
1NAME1NAME2NAME3NAME4NAME5move street address here
2E P=& D F ROCHE MARCO IS TRUSTUTD 10/10/98320 SEABREEZE DR320 SEABREEZE DR
3MYRNA M MOONAN-DIAZ TRDIAZ FAMILY TRUSTUTD 10/1/101289 FRUITLAND AVE1289 FRUITLAND AVE
4RICHARD ZIC LIVING TRUSTZIC TR, MAURA=& RICHARDMAURA ZIC LIVING TRUST6760 N LEOTI AVE6760 N LEOTI AVE
5SHARON D DELAWDER LIV TRUST340 SEABREEZE DR340 SEABREEZE DR
6BOYCE, THOMAS HJOAN SCHADE-BOYCEELIZABETH BOYCE TRKEVIN C O'NEILL TR88 MADAGASCAR CTKEVIN C O'NEILL TR
Sheet1
Cell Formulas
RangeFormula
F2:F6F2=LOOKUP(2,1/(A2:D2<>""),A2:D2)


Its mostly working now. except for row 6
NAME1NAME2NAME3NAME4NAME5move street address here
E P=& D F ROCHE MARCO IS TRUSTUTD 10/10/98320 SEABREEZE DR320 SEABREEZE DR
MYRNA M MOONAN-DIAZ TRDIAZ FAMILY TRUSTUTD 10/1/101289 FRUITLAND AVE1289 FRUITLAND AVE
RICHARD ZIC LIVING TRUSTZIC TR, MAURA=& RICHARDMAURA ZIC LIVING TRUST6760 N LEOTI AVE6760 N LEOTI AVE
SHARON D DELAWDER LIV TRUST340 SEABREEZE DR340 SEABREEZE DR
BOYCE, THOMAS HJOAN SCHADE-BOYCEELIZABETH BOYCE TRKEVIN C O'NEILL TR88 MADAGASCAR CTKEVIN C O'NEILL TR
 
Upvote 0
Fluff said:
That is not what I get.
Fluff.xlsm
ABCDEF
1NAME1NAME2NAME3NAME4NAME5move street address here
2E P=& D F ROCHE MARCO IS TRUSTUTD 10/10/98320 SEABREEZE DR320 SEABREEZE DR
3MYRNA M MOONAN-DIAZ TRDIAZ FAMILY TRUSTUTD 10/1/101289 FRUITLAND AVE1289 FRUITLAND AVE
4RICHARD ZIC LIVING TRUSTZIC TR, MAURA=& RICHARDMAURA ZIC LIVING TRUST6760 N LEOTI AVE6760 N LEOTI AVE
5SHARON D DELAWDER LIV TRUST340 SEABREEZE DR340 SEABREEZE DR
6BOYCE, THOMAS HJOAN SCHADE-BOYCEELIZABETH BOYCE TRKEVIN C O'NEILL TR88 MADAGASCAR CT88 MADAGASCAR CT
Summary
Cell Formulas
RangeFormula
F2:F6F2=LOOKUP(2,1/(A2:E2<>""),A2:E2)


Can you post your data using the XL2BB add-in so that we can see what your formula is doing.
Click to expand...
Not sure if I uploadrd xl2bb clip right but will try again
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Copy Entire Row to another worksheet if Sum in a cell is greater than a certain number
Replies
9
Views
1K
Michael M
Michael M
M
  • Question
Delete entire rows that contain any of the values contained in a seperate list
Replies
4
Views
1K
kevatarvind
kevatarvind
B
  • Question
copy cells from one worksheet to another based on condition and put in new cells in multiple rows
Replies
0
Views
1K
breilly00
B

Forum statistics

Threads
1,214,979
Messages
6,122,557
Members
449,088
Latest member
davidcom

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