• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Getting data before space

mrzoogle

Member
Hi All,


I was trying to clean the raw data and faced a situation which I need help.


Raw Data


abc 1

abcd 11

abc 22

abcde 14


The len is not constant.


How can I just get the data before the space using a formula.


So the result after the formulae would be:


abc

abcd

abc

abcde


I tried to use mid,left or right. I also give it a go with substitute formula but no luck. Maybe I am missing out something very simple ?


Please help me to slove this issue. Thanks for your time.


Regards,

Z.
 
Hi mrzoogle,


Assuming you data is in column A, enter this and drag down:
Code:
=MID(A1,1,FIND(" ",A1))


Regards,

Faseeh
 
Ahh this is great and cool trick. Thank you very much Faseeh & oldchippy !!!


Can I ask you a question please ? How can I improve my skill in being able to solve this kind of issues. I know quite alot of formulae and VBA but still I tend to get stuck in few tricky situations like this & with array formula!!! Is that experience speaks alot or I just need to do alot more research than I currently do ?


Thanks for your quick responses and hoping to get some ideas to increase my excel knowledge.


Regards,

Z.
 
Hi, mrzoogle!

If you don't want to write that much, try this:

=LEFT(A1,FIND(" ",A1)-1)

:) one character is one character

Regards!


@Faseeh

Hi!

Getting overpayed again? You have to reimburse the 0.15$ of that char... Don't forget!

;)

Regards!


@oldchippy

Hi!

First time I make it shorter.

Regards!
 
Hi mrzoogle,


Another way without using formula's is to use Data > Text to columns and use the space as the Delimiter......that could be shorter hey SirJB7?
 
@oldchippy

Hi!

For sure it is... :(

Regards!

PS: ... but it all depends on the times it's gonna be done ... :)
 
@oldchippy,


Yup that single space was extra! :)


@mrzoogle,


Keep replying (..and trying) just like we do. Nothing special to learn this.


@ SirJB7...Hi,


Have you seen my 'other' post concluded by Narayyan? ;P .. will be jealous to see how much i am earning these days...!! :D Lolz


Regards,

Faseeh
 
@Faseeh

Hi!

Not seen yet, gonna search now...

But let's get focused: you get paid 0,15$ (or were it €? or ₤?) per char and the 20% is mine. That was the deal.

Don't forget transfer. Thanks.

Regards!

PS: It's nothing about jealousy, it's just money, hahaha


EDIT:

I read it, and congrats on the use of INDIRECT function, welcome to the jungle :)
 
@SirJB7,


OK Sir, you got that ($,€ or ₤?, whichever has higher exchange rate)!!! :D hehehe, ...and thank you for INDIRECT function, I am still learning. :)


Regards!
 
Back
Top