Separating block of text into columns

Resolved · Medium Priority · Version 2016

Fri 5 Feb 2021, 11:05 Delegate Marie said...

Marie has attended:
Excel Intermediate course
Excel Intermediate course

Separating block of text into columns

Hi, I have a very long list of info on a pdf that is separated in headings ie name; address; tel. When I copy and paste it to excel, they merge together into one column. How do I keep them separated please? I've tried pasting them to notebook first but didn't help. Thanks.

Fri 5 Feb 2021, 14:16 Trainer Jens said...

RE: Separating block of text into columns

Hi Marie,

Thank you for the forum question.

When you copy from a text document or PDF the text is not organised in columns but only in lines.

That is the reason for you get it in Excel in one column and many rows.

Try the Text to Columns tool (see my other answer). If it is not working Excel can only help you (and it will be complicated) if there is a clear pattern in you data. A pattern could be that all the names were one first name and one last name.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Fri 12 Feb 2021: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Use RANDBETWEEN to generate random numbers

There may be occassions where you need to generate random numbers in your spreadsheet. Use the RANDBETWEEN function to generate random numbers between two values that you specify.

The function looks like this:

=randbetween(LOW,HIGH)

where LOW is the lowest number you want generated; and HIGH is the highest number you want generated.

This formula will work with both positive and negative LOWs and HIGHs. Also it will only generate integer numbers unless forced to do otherwise by the following:

=randbetween(LOW*10^PRECISION,
HIGH*10^PRECISION)/(10^PRECISION).

where PRECISION represents the levels of decimal precision needed (i.e. if you need numbers with one decimal place, PRECISION would be 1; 2 for two decimal places and so on).

One final note, if the RANDBETWEEN formula does not work in your spreadsheet or returns a "#NAME" error, you need to install the Analysis Toolpak Add-In. You will need to press F2 then Enter following the installation.