Adding all data into 1 cell from a row ignoring blanks and adding title before whats in non blank cells

scottyyboyy

New Member
Joined
Feb 2, 2015
Messages
41
Hi All,

I have searched all over the net and cant find the answer.

I have a spreadsheets with hundreds of thousands of rows and hundreds of columns, I need to pull out the data that's in a row and add all into 1 cell but where it finds data in a row i need it to take the title name to the word it finds in the row.

hope this makes sense please see below

emp no.Found datafirstnamelast nameagesexaddress 1address 2 Postcodedob

1
dave smith 22 wheel rd b22 2bbdavesmith22wheel rdb22 2nn
2scott 22 m 33 drive rd b22 2nn 19/02/1988
scott22m33 drive rdb22 2nn19/02/1988
3james bob m 22 b22 2nn 20/03/1957jamesbobm22b22 2nn20/03/1957


<tbody>
</tbody>


the green text is what i need but this is for 100,000 rows and goes to like column VZ ish.

Thanks in advance if someone can help me
Scott
 
You're welcome & thanks for the feedback
 
Upvote 0

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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