Sorting cell that contains TEXT only

Hi All,

I'm a newbie in this forum I need help with my project in which I have to deal with various type of data.

So, I have big data in excel that require me to sort out the cell that contain "TEXT" only value. Below I enlist some dummy data which pretty similar with the one I have to deal with.


A | B |
0303-numbers
0303numbers
0313so
031v13807x
0330-numbers
0333-numbers
BOOKISGOOD
careers-seminar
newyork
holland-newyork
123456



From the above data, I need to get the cell that contain TEXT only (no dashes, no numbers) which are: "BOOKISGOOD" and "NEWYORK". So maybe for this cell I want a remark "X" in column B.

As for the cell that contain "dashes"/ (-) with "Y" remark in column B, and NUMBERS only cell with "Z" remark, and cell with combination of TEXTS and NUMBERS with "O" remark.

I really appreciate if anyone could help me to share some insights how to accomplish this project.

Thanks in advance

It should get "Y" remark since it has dash in them

Originally Posted by dredwolf

what about numbers and text with a "-" in them?
(ie:0303-numbers)

Originally Posted by :) Sixthsense :)

In B1 cell

Formula:

Please Login or Register  to view this content.

Hi Sixthsense,

I really appreciate your quick help and response Never joined any forums before that response to the help inquiry so fast and responsive like this forum.

However, I think although it's "close enough" but I think it's still required some adjustments

Below is the result after trying your formula:

name | Sixthsense's | Should be
-----------------------------------------------------
0303-numbers | O | Y
0303numbers | X | O
0313so | X | O
031v13807x | X | O
0330-numbers | O | Y
0333-numbers | O | Y
BOOKISGOOD | X | X
careers-seminar | Y | Y
newyork | X | X
holland-newyork | Y | Y
123456 | X | Z

I'm really sorry if it seems very complicated. And at the moment the most important thing that I need to sort out from this data is how I can get the "X" remark correctly (the cell with ALPHABETS/TEXT only and no NUMBER or DASH at all).

So, based on the dummy data there should be only 2 "X" The other remarks are just my precautionary, just in case those data will be required in the future too.

Many thanks in advance for the helps.

Originally Posted by :) Sixthsense :)

Thanks for the feedback

One request from my end please attach your sample data with expected output which will be very much helpful for our workings and better understanding of your data structure.

Uhm, can you tell me how to attach file? I can't find the option to attach file. However, as the alternative i attached screenshot of the excel instead. Hope it could help you to get better picture of the expected result.

Excel_forum.PNG

Many thanks in advance ;-)

Hi -

I borrowed zbor formula and amend to suit for your needs.

Please Login or Register  to view this content.

Regards,
Event

Thanks guysssssssss It's solved now though I'll need to spend sometime later to learn about the formula. I am not familiar with the functions used above. I think I really have to learn a lot from you guys

Thanks once again

try it (array formula)
=INDEX(({"X";"O";"Z";"Y"});MATCH(COUNT(IFERROR(MID(SUBSTITUTE(A1;"-";REPT(0;LEN(A1)+1));ROW($1:$100);1)/1;""))/LEN(A1);({0;0,0001;1;1,0001})))

Originally Posted by Ghozi Alkatiri

try it (array formula)
=INDEX(({"X";"O";"Z";"Y"});MATCH(COUNT(IFERROR(MID(SUBSTITUTE(A1;"-";REPT(0;LEN(A1)+1));ROW($1:$100);1)/1;""))/LEN(A1);({0;0,0001;1;1,0001})))

Although, it's been solved. Thanks also to Ghozi