Function for finding various values and replacing with different character based on result

geepy

New Member
Joined
Jan 16, 2019
Messages
6
I think what I'm trying to do is quite straight forward, but I just can't find the right functions or combination of functions to do - partly because some of the obvious functions don't allow wildcards.

I have a column of cells which could contain one of five letters as the first character (P, L, E, D, or A), but after the letter there are some more characters which can vary. For example:


P (2/2)
L (2/1)
A (0/2)
P (1/2)
E (2/2)
D (0/2)

I don't need the data after the first character (the letter) and want to find quick way of deleting it to just leave the first character in the cell. But because the first character could be one of five different letters, I need a function to first check which letter the first character is, and then to replace all the characters in the cell with that first letter (e.g. If it finds 'P (2/2)', replace it with just 'P'.

The following formula is not correct and is not really a formula, but hopefully it will show you what I'm trying to do: =IF("P*", replace with "P"), IF("A*", replace with "A") etc.

I want to use the wildcard because I don't care about the data after the first letter, but I want one formula which can check every cell, find out what the first letter is, and then replace all of the characters in that cell with only that first letter. I can do with fairly quickly with the Find/Replace tool in Excel, but the problem is that I have to perform the search multiple times to cover the different combinations of (2/2), (1/2), (0/2) etc., so it's not ideal.

Very grateful for any help you can give!
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,578
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel
Try
=LEFT(A2,1)
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,578
Office Version
  1. 365
Platform
  1. Windows
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,109,451
Messages
5,528,836
Members
409,839
Latest member
akashsadhu
Top