Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / November 2007

Tip: Looking for answers? Try searching our database.

From Column A get Text from Colum B

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
DaveM - 10 Nov 2007 13:44 GMT
Hi

I have text cells in column B, The text cells in column B start with some
word or words followed by a space-space then the rest of text.

Example:

Cell B1

Analogue (Analog) circuit - An electronic circuit in which an electrical
value (usually voltage or current, but sometimes frequency, phase)
represents something in the physical world.The magnitude of the electrical
value varies with with the intensity of an external physical quantity.

How could I get "Analogue (Analog) circuit" and put it in column A1.

There is lot of cells in the columns and I'd like to go down one at a time,
and move these to column A.

Thanks in advance

Dave
Dave Peterson - 10 Nov 2007 14:07 GMT
=search(" - ",b1)
will return the position of that " - " string.

So
=left(b1,search(" - ",b1)-1)
will return the beginning of that string in B1

If there's a chance that there's no " - " in that string, you can avoid the
error and bring back everything with a formula like:
=left(b1,search(" - ",b1&" - ")-1)

> Hi
>
[quoted text clipped - 18 lines]
>
> Dave

Signature

Dave Peterson

DaveM - 10 Nov 2007 14:17 GMT
Hi Dave

Works a treat

Thanks for your fast reply

All the best

DaveM

> =search(" - ",b1)
> will return the position of that " - " string.
[quoted text clipped - 32 lines]
>>
>> Dave
Don Guillett - 10 Nov 2007 14:13 GMT
With a formula
=LEFT(I22,FIND("-",I22)-2)

a macro

mr=range("i22")
mr.value=LEFT(mr,FIND("-",mr)-2)
Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hi
>
[quoted text clipped - 18 lines]
>
> Dave
Rick Rothstein (MVP - VB) - 10 Nov 2007 19:29 GMT
> a macro
>
> mr=range("i22")
> mr.value=LEFT(mr,FIND("-",mr)-2)

I believe it should be more like this...

Set mr = Range("B1")
mr.Offset(0, -1).Value = Left(mr.Value, InStr(mr.Value, "-") - 2)

Rick
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.