MS Excel: split up data in a cell and put it into 2 cells.

[STEINER]

Hi,

I was familiar with Excel in Microsoft Office Professional 2003 ( I think 2003).

Laptop has Office 2010. I used a function in MOP 2003, havn't used it in a few months, forgotten how to do it! I can't find it, searched a good bit for it.

All I am trying to do is split up data in a cell and put it into 2 cells. It is the opposite of CONCATENATE.

55MB
66MB
121MB

I want to put 55 in 1 cell in column1 and MB in an adjacent cell in column2, ie separate the data.

COLUMN1 COLUMN2

55 MB
66 MB
121 MB


Help appreciated.

 

[ajapale]

Is it allways MB?

If so this works.


Highlight Data
Data
Text to Columns
Delimited
Other use "M" as delimiter
Search and Replace B with MB

 

[jpd]

Try this function in A2 where "12MB" is in cell A1

=VALUE(LEFT(A1,SEARCH("MB",A1)-1))

 

[STEINER]

Is it allways MB?

If so this works.


Highlight Data
Data
Text to Columns
Delimited
Other use "M" as delimiter
Search and Replace B with MB

Thats grand, thanks. I just couldn't remember the thing. Its ages since I used it, but I used it a lot, my memory needs boosting. I'll look at it later.

MB is just megabytes. Sometimes there is KB for kilobytes. With o2 I can see my mobile data usage online per session and per day . I just wanted to take the MB out into a separate column so that I could sum the individual data usages and check that they tally with the figure for data used on the bill.

 

[Homer]

An even easier option would be to use Ctrl H and replace MB with nothing.

 

[JoeRoberts]

Another more variable solution is as follows:Useful if want to split cells based on the character position rather than say "MB".

In adjacent column cell (say b1) put this formula:
=length(a1)-2
In next column ( say c1) put:
=left(a1,b1)
In d1 then
=right(a1,2)

Hide the columns you don't need.