Extract unique, consecutive rows, first character from string

[ionelz]

I have in Column A these strings, I would like to extract First character only, unique, in consecutive rows
Column A
A222
A333
B123
D345
A678
Z256
A456
B777

At B1 result should be :
A
B
D
Z

[alansidman]

With Power Query called Get and Transform in your version and found on the Data Tab

Please Login or Register  to view this content.

[Czeslaw]

Apply the formula

Please Login or Register  to view this content.

[mehmetcik]

This works on my version of excel
Available for:

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel for iPad Excel for iPhone Excel for Android tablets Excel for Android phones.

The UNIQUE function returns a list of unique values in a list

Formula for B1 =UNIQUE(LEFT(A1:A8,1))

[Bo_Ry]

Please try at B1

=IFERROR(CHAR(AGGREGATE(15,6,CODE(A$1:A$8)/(FREQUENCY(CODE(A$1:A$8),CODE(A$1:A$8))>0),ROWS(B$1:B1))),"")

[ionelz]

For some reason #5, I can not make it work

[AliGW]

Is that it? What about the other two suggestions? And what about the detailed response I gave in your other thread? If people bother to try to help you, at least have the courtesy to acknowledge it.

[ionelz]

All ok, I have left them feedback, just wanted to let Bo know that something I can not make it work in his formula, usually he is very good at

[Bo_Ry]

When something doesn't work, please provide more detail and attach the workbook.

[alansidman]

All ok, I have left them feedback, just wanted to let Bo know that something I can not make it work in his formula, usually he is very good at

Really, and what form of communication did you use. I have not received any acknowledgement or information from you regarding my proposed solution.

[AliGW]

Nor have I, Alan, for the detailed response I gave in the other thread. Seems like common courtesy is too much to ask.

[alansidman]

@AliGW--Seems to be the way of life these days in forums. Truly sad. Wonder why I go to the trouble to offer help some days.

[ionelz]

Bo, thanks for attached file, now all ok !

[Bo_Ry]

@ionelz Did you read Alansidman, AliGW, Mehmetcik's comments?

I would not happy to offer free help and not get any feedback.

[ionelz]

Sorry, something must be wrong, I left reputation to ALL which help on this !
But : #2 works but I wanted Formula, #3 works, #4 I was not able to use it since I do not have Unique, and #5 and #9 works
AT #9, only if you want, I still have an issue, but that because I was not explained right at beginning.
If I have at least one EMPTY row, it doesn't work
At H4, if I reduce Table height from F16 to F12 it works, but with empty rows no

[alansidman]

Thanks for the detailed feedback.

[wk9128]

Response #15

H4 cell , formula , Drag down

Bo_Ry #9, the formula can still be used, just a small modification

Array formula , This formula is better and can be arranged in order, from small to large

[Bo_Ry]

Pleas try
with sort
=IFERROR(CHAR(AGGREGATE(15,6,CODE(B$4:B$16)/(FREQUENCY(CODE(B$4:B$16&" "),CODE(B$4:B$16&" "))>0),ROWS(D$4:D4))),"")

or no sort
=IFERROR(LEFT(INDEX(Table1[Cells],MATCH(TRUE,INDEX(ISNA(MATCH(LEFT(Table1[Cells]),H$3:H3,)),),))),"")