[Don Durkes]

I have a regular spreadsheet with several columns that are reported in either Kbps or Mbps. For example:

Type Speed
Video 800 Kbps
Video 1.25 Mbps

I would like to find all references to Mbps, delete the text and multiply by 1024 to convert everything to a common measure of Kbps. I'll simply delete all the Kbps text subsequently.

How would this best be done? I'm a putz at macros and VB, and a lot of other things, so if there's an application one knows of I would appreciate it.

Thanks

[Advertisements]

first...wrong area of the forum...we've got an office forum specifically for office questions...i'll move this thread there for you

second...

in the column directly to the right of the speed column...enter this next to the first cell with a speed value...then copy it down the line and check the numbers to make sure they're right

=IF(ISNUMBER(SEARCH("mbps",B2)),LEFT(B2,4)*1024, LEFT(B2,4))

the "b2" assumes that the speed is in column b and starts in cell 2...if it doesn't...move it to where it should be..

also...the "left(b2,4) assumes that the first 4 characters in the speed column are numbers and they there are only 4 numbers in the speed of the video (which given the scale that you've shown...i would imagine the speeds are either in 3 digit kb numbers (000 to 999) or in 4 character mb numbers (0.00 to 9.99) )

the biggest problem is that you're dealing with numbers and text in the same cell

[dsenette]

first...wrong area of the forum...we've got an office forum specifically for office questions...i'll move this thread there for you

second...

in the column directly to the right of the speed column...enter this next to the first cell with a speed value...then copy it down the line and check the numbers to make sure they're right

=IF(ISNUMBER(SEARCH("mbps",B2)),LEFT(B2,4)*1024, LEFT(B2,4))

the "b2" assumes that the speed is in column b and starts in cell 2...if it doesn't...move it to where it should be..

also...the "left(b2,4) assumes that the first 4 characters in the speed column are numbers and they there are only 4 numbers in the speed of the video (which given the scale that you've shown...i would imagine the speeds are either in 3 digit kb numbers (000 to 999) or in 4 character mb numbers (0.00 to 9.99) )

the biggest problem is that you're dealing with numbers and text in the same cell

[Don Durkes]

Sorry about the post in the wrong area. I will pay better attention next time! Thanks for moving it.

Thanks also for your help. This is EXACTLY what I needed. The problem you identified is precisely it - text AND numbers. It's been driving me nutty.

Don

first...wrong area of the forum...we've got an office forum specifically for office questions...i'll move this thread there for you

second...

in the column directly to the right of the speed column...enter this next to the first cell with a speed value...then copy it down the line and check the numbers to make sure they're right

=IF(ISNUMBER(SEARCH("mbps",B2)),LEFT(B2,4)*1024, LEFT(B2,4))

the "b2" assumes that the speed is in column b and starts in cell 2...if it doesn't...move it to where it should be..

also...the "left(b2,4) assumes that the first 4 characters in the speed column are numbers and they there are only 4 numbers in the speed of the video (which given the scale that you've shown...i would imagine the speeds are either in 3 digit kb numbers (000 to 999) or in 4 character mb numbers (0.00 to 9.99) )

the biggest problem is that you're dealing with numbers and text in the same cell

Edited by Don Durkes, 19 August 2009 - 10:18 AM.