Ask Experts Questions for FREE Help !
Ask
    saatt79's Avatar
    saatt79 Posts: 3, Reputation: 1
    New Member
    		  
    #1

    Sep 17, 2007, 09:39 AM
    Ms excel
    Hi,

    Say I have multiple data in a spread sheet like,

    A
    1 DAD111

    2 CATUS25

    3 58UTIOPS

    I want a output like this

    Numeric separately and alphabets separately

    DAD in one cell and 111 in another cell
    CATUS in one cell and 25 in another cell
    UTIOPS in one cell and 58 in another cell

    Is it possible ?:confused:
    nicespringgirl's Avatar
    nicespringgirl Posts: 1,237, Reputation: 187
    Ultra Member
    		  
    #2

    Sep 17, 2007, 09:44 AM
    Data->Text and column... follow the instructions you'll get what you want.;)
    saatt79's Avatar
    saatt79 Posts: 3, Reputation: 1
    New Member
    		  
    #3

    Sep 18, 2007, 08:30 AM
    Quote Originally Posted by nicespringgirl
    Data->Text and column...follow the instructions you'll get what you want.;)

    Hi, I know this Text to columns formatting, but if I have some kind of formulaes it would serve me better. Because

    For Ex if the data are not in same format like

    Dad1233
    Ghtathe1589dgk
    Adkioejodoejj58964lll

    I am not able to split the alphabets and numerics in a separate cells

    Thanks
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
    		  
    #4

    Sep 18, 2007, 08:41 AM
    Text to columns doesn't really work here. Since you don't have a real pattern that you can match to, this will be very difficult.

    You can do it better in Access, because the VAL function will return the numeric portion of a text string. So VAL("123abc") returns 123. So you could build a function that extracts the number, converts it back to a string, then replaces it with nothing in the original string.
    saatt79's Avatar
    saatt79 Posts: 3, Reputation: 1
    New Member
    		  
    #5

    Sep 18, 2007, 08:53 AM
    Quote Originally Posted by ScottGem
    Text to columns doesn't really work here. Since you don't have a real pattern that you can match to, this will be very difficult.

    You can do it better in Access, because the VAL function will return the numeric portion of a text string. So VAL("123abc") returns 123. So you could build a function that extracts the number, converts it back to a string, then replaces it with nothing in the original string.

    Hi Scott,

    Thanks for your reply, but the worst part is I am zero in access and I don't know how to build queries or table in MS Access, but I have some knowledge in MS Excel.

    Thanks for your support
Not your question? Ask your question View similar questions
 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Ms excel [ 1 Answers ]

Can you give me the meaning of cell adverg

MS Excel [ 2 Answers ]

I am modifying a payroll spreadsheet that was developed in MS Excel. The hours worked by each employee have their own row; each cell represents the hours worked on that particular day. I need to isolate ONLY the amount greater than eight hours (for overtime) and sum those quantities at the end of...

How to get better with Excel [ 2 Answers ]

My Vietnamese staff and I use Excel all the time, but we're limited to very basic stuff. Can anyone recommend an easy and comprehensive how-to reference, either book form (which we would have to order by mail) or on-line info? Thanks. -- Chuck Searcy, Hanoi

Excel [ 2 Answers ]

:rolleyes: I try to use excel to print an invoice, how can I put an invoice number that will auto upadate the next time I use the file?

Excel help [ 1 Answers ]

I am using excel to record some data and I have it setup to do some math based on the info that I put into certain cells. Now what I am wondering is am I able to setup the sheet so that it will prompt me for the 4 pieces of data that I want to enter and then place those in the proper cells and then...

View more questions Search