Segregating data written in multiple lines in a single cell

We have been pressing Alt + Enter key to write in new line in same cell. However what about the vice versa case. If I get a list of countries all written in same cell; Of course I would not be able able to use any formula on that unless they are written separately, i.e each county name should be written individually in separate cells. So what is the solution?

Lets take a case study

Lets say I have copied a list of countries from a pdf file, which when pasted in Excel is pasted as shown below:

Now the challenge is I have to put all the countries in a validation list. Thus, for that my first task is they should be in separate cells, else they will be treated as single value.

Solution:

We are gonna use Text-To-Column wizard

So this is how we do

Step 1 : Select the cell that contains the names of countries (Cell B3 above).

Step 2 : Go to "Data" tab in the ribbon and select "Text-To-Columns".

Step 3 : Select the option as "Delimited" on the first page of the ribbon.

Step 4 : Select "Other" as the option in screen  2 of the wizard and

             press Ctrl + J on your keyboard.

             Notice the county names now shown in separate column 

             in the preview section of the screen.

             Now click Next.

Step 5 : Select a destination cell on screen 3 of the wizard and click finish.

You will notice the country names divided in cells.

Explanation:

Pressing Ctrl + J on the keyboard is the identifier of new line in Excel. We can use this in Find and Replace too. So suppose I want to add a hyphen ("-") at the end of each countries name. For this I can go in Replace dialogue box; Find Ctrl + J and Replace with - (Hyphen).

Getting Images in an Excel Sheet from a folder

Now moving a bit towards programming .....

So somebody asked me "If he has to get hundreds of pictures from a folder and arrange them in a column" when the names of pictures are already mentioned in the file ... should he spend his entire day doing that??

The answer is NO... Why not to automate the entire process .. Yes It can be , the solution is as below:

Step1 : List the names of Pictures in a single column (See depiction below for example)

Step2 : Now at the back-end, write this code


Sub Get_Images()

Dim FolderName As String
'statements to select Folder
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    If .Show = -1 Then
 
        FolderName = .SelectedItems(1)
    End If
End With

LstData = Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))

For i = 2 To LstData
nm = Sheet1.Range("a" & i) 'statement to get the names of images from sheet
Rows(i & ":" & i).RowHeight = 111
ActiveSheet.Pictures.Insert(FolderName & "\" & nm & ".jpg").Select
Selection.ShapeRange.Width = 100
Selection.ShapeRange.Height = 95
Selection.Cut
Range("C" & i).Select         'Pictures will be arranged in column C
ActiveSheet.Paste
Next i

End Sub

Step3 : Run the code by pressing F5

And the Task is accomplished.....


Keep Rocking !!! All the best .... 

Repeat labels in all Rows

Welcome Readers !

So here is a simple solution to question which many people ask me during my sessions.

Imagine when you get data in form of a pivot Report . As a simple step , you just have to copy and paste this data from pivot in a new sheet and start working on it. But can you use filters ?? Can you use formulas through out ?? The answer is NO .. But why ?? Because the first column that contains some category names ,have it written only on the top row, for others it is blank .. Right ????

So does your data looks something like this ?

Can you notice the Dept Column ? Where Department names are written just once in the top row of its particular group.

So the solution is something very simple

Here you go ...

The only thing you need to do is , Go in the very next cell of the first category name in this column. Here type the formula beginning with equal symbol (=) and select the cell above.
For example in above image, if  'Dept' is written in column 'A' and 'Admin' is written in cell 'A2' , then go in cell A3 and type the formula as =A2 and press enter.

Now copy this cell (In this example cell A3)

Next step in select this entire column and press F5 on your keyboard . This will take you to Go To dialogue Box. Now hit the 'special' button given at the bottom of this dialogue box and select the option as 'Blanks' and click 'OK'.
Can you notice all blank cells in this column are selected. And now nothing , just press Ctrl+ V to paste.

Result : Each row in data has category name written in front of it.

Hope this helps ....