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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,404
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
48,404
Office Version
  1. 365
Platform
  1. Windows
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,114,472
Messages
5,548,241
Members
410,824
Latest member
Bobmn4
Top