Splitting a cell's data 5

[sandybam]

I have a spreadsheet that houses both phone and correspondence statistics within the same cell seperated by a /. For instance 2/5. I would like to split these into two seperate columns of information. I tried to use the LEFT and RIGHT functions but the number of characters are not always the same. Is there a way to split the cell somehow referencing the /?

 

[Sypher2]

Try this. Not sure if it will work for you or not.

For numbers before the "/".

=MID(A1,1,FIND("/",A1)-1)

For number after the "/".

=MID(A1,FIND("/",A1)+1,25)

If 25 characters isn't long enough or too long, you can replace that number in the second formula. Also, replace the cell A1 with whatever cell you are evaluating. Hope that helps.

 

[DaleWatson123321]

sandybam,

Sypher2's solution is a GOOD one - worthy of a STAR.

The only thing I would add, is the use of a LARGE number in place of the "25".

Whenever "pulling" characters from the "right" side of text, the "Mid" function has no problem with using a number such as "999".

Using "999" as a "standard", I never have to worry about the actual number of characters.

Hope this also helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[sandybam]

Your suggestion works for all of the zero values ("0&quot but returns an error $VALUE# for all non-zero values. Any suggestions as to what I may be doing wrong?

Sandy

 

[DaleWatson123321]

Sandy,

In trying to create your problem, I noticed that when I copied the "0/5" down to additional rows, and then replaced the "0" with another number less than 12, Excel immediately interpreted the change as being a "date". This, in turn, caused the $VALUE# to show up in the adjacent formulas.

The solution I used was to format the entire column as "TEXT". To do this for the entire column, just right-click on the column letter, and choose "Text".

Once changed to "Text", changing the "0" to another number did NOT cause a problem.

If any of your cells will be "BLANK", then you should use the following formulas:

=IF(A1="","",MID(A1,1,FIND("/",A1)-1)) and

=IF(A1="","",MID(A1,FIND("/",A1)+1,999))

Hope this resolves your problem. Please advise.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca.

 

[DaleWatson123321]

Slight addition...

To change the formatting of the entire column, I missed the instruction to select "Format Cells" after right-clicking on the column letter.

... Dale Watson

 

[sandybam]

When I try to change the formatting to text, general or a number Excel replaces my values with numbers. For instance:

3/00 now equals 36586
4/00 now equals 36617
8/00 now equals 36739

no matter which format I use. I have had this problem in the past and no one knows why this happens. Is it the way my Excel is set up?

Sandy

 

[DaleWatson123321]

Sandy,

I've found that once a cell with "3/00" (for example) has been formatted to "General" or "Number", Excel internally treats the value as a "date". The large numbers (e.g. 36586) represents the number of days which have elapsed since January 1, 1900. Therefore, this indicates that Excel has ALREADY done the "conversion" to a "date".

IF you happened to still have access to a copy of your data in "original" format (before being converted by Excel - by being formatted as something other than "Text", then I am confident (just tested to confirm) that IF you FIRST format the entire column as "TEXT", then this will permit the formulas to work.

Please advise.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[sandybam]

Thank you so much for the insight. That type of problem has been a thorn in my side. Unfortunately, the data is extracted from one of our archaic programs that automatically formats the spreadsheet. But the good news is that I finally know why this is happening. I'll speak with one of our Systems folks and see what they can do for me.

I really appreciate your time and expertise!

Sandy

 

[ETID]

Won't the "text to columns" tool work here as well?

highlight the column to split, then

from the menu:
data>text to columns>delimited>click other and declare a "/" as a delimiter

 

[DaleWatson123321]

ETID,

EXCELLENT !!! I didn't know this was there. THANKS - and worthy of a STAR.

The only thing I would add is that after choosing the "Next >" screen, under "Column data format", there is an option to select "text". This option applies to each of the fields listed below under the heading "Data preview". Therefore, one would select the field(s) FIRST, and then choose the "text" formatting option.

I believe that in Sandy's case this should work, even without using the "text" option, because the result desired will probably be a "number" (just NOT a "date" number).

However, for others who might have similar situations, but DO require a "text" result, it is GOOD to know that this is an option ...to convert to TEXT.

Thanks once again for your contribution.

Sandy, I hope this works for you. Can you please advise - for ALL Tek-Tippers.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[sandybam]

This worked like a charm!

Thank you all for your help!
Sandy