Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

conditionally delete many cells containing text in Excel


  • Please log in to reply

#1
Don Durkes

Don Durkes

    New Member

  • Member
  • Pip
  • 3 posts
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
  • 0

Advertisements


#2
dsenette

dsenette

    Je suis Napoléon!

  • Administrator
  • 26,019 posts
  • MVP
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
  • 0

#3
Don Durkes

Don Durkes

    New Member

  • Topic Starter
  • Member
  • Pip
  • 3 posts
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.

  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP