Help finding first letter of data

Dan Wilson

Well-known Member
Joined
Feb 5, 2006
Messages
504
Office Version
  1. 365
Platform
  1. Windows
Good day. I am running Excel out of Office365 (updated) on Windows 10 Home (updated). I have a workbook containing 3 worksheets. One worksheet is titled "50-69" and contains 3000 rows with data of songs. Another worksheet titled "Counts" contains formulas indicating totals based on various items of data from "50-69". Column A of "50-69" contains the title of each song. I have tried several different functions trying to identify how many songs start with each letter of the alphabet and a few special characters such as Parenthesis and Double-Quote. All I need is one formula to identify the count of songs starting with character "A" and I can then extrapolate the formula to find the rest. I went through the Help feature but found no answer.
Thank you for any help.
 
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
just another option to try

Countifs.xlsm
ABCDE
1(1
210
3a2
4h1
5y3
1d
Cell Formulas
RangeFormula
E1:E5E1=SUMPRODUCT(--(LEFT('50-69'!$A$2:$A$4000)=A1))
Good day Dave Patton and thank you for responding. It appears there are several ways to solve my question. I will experiment with your suggestion and hopefully learn something new.
 
Upvote 0
I narrowed the search to each character of the alphabet, a parenthesis and numbers 1-9.
In that case, if you want, you can get all the counts with a single formula in a single cell, rather than copying down. The formula in B1 below automatically 'spills' all the other results down the column.

Dan Wilson.xlsm
A
1Song
2A Hard Day's Night
3(I Can’t Get No) Satisfaction
4You Belong To Me
510 Commandments
6Hey Jude
71000 Nights
8Another Brick in the Wall
9Yesterday
10Yeah!
11
50-69


Dan Wilson.xlsm
AB
1A2
2B0
3C0
4D0
5E0
6F0
7G0
8H1
9I0
10J0
11K0
12L0
13M0
14N0
15O0
16P0
17Q0
18R0
19S0
20T0
21U0
22V0
23W0
24X0
25Y3
26Z0
2712
2820
2930
3040
3150
3260
3370
3480
3590
36
Counts
Cell Formulas
RangeFormula
B1:B35B1=COUNTIF('50-69'!A2:A5000,A1:A35&"*")
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

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