Hi guys,

We've got a complicated process to post images of our thousands of parts into our database system.

Currently I have to get the file address of the image, such as:
docs.google.com/file/d/THISISTHETEXTINEED/edit?usp=sharing

The 'THISISTHETEXTINEED' section is, of course,the chunk I need to extract.

I can get this by using the left and right functions
=RIGHT(Q22, LEN(Q22)-31)
and
=LEFT(R22, LEN(R22)-17)

However, I'd like to be able to use these two functions in one cell, and I have no clue how to do that - can someone please show me how to combine these two functions in one cell?

Additionally, I need to be able to copy the output from excel into our database. The problem is that when the functions output data, it shows the data, but if you copy the cell, you only copy the function, not the output value. I can copy the cell containing the function, and paste right next to it with 'paste special - value. This will then show just the 'THISISTHETEXTINEED' data, in text, which I can then copy out. However, I'd like to be able to make Excel output that data as text automatically.

In other words, if you have a cell that adds the values of two cells (=A1+A2) and the result is ten, if you copy that cell, you won't get 10, you'll get =A1+A2. I want to copy the cell and get the value, not the function.
Or, more specifically, I'd like for the results to paste special-value into an adjacent cell automatically, without me having to copy and paste it each and every time. Is there a way to do this?

Thanks guys!

Your explanation suggests all text you need to extract from will be the
same before/after 'THISISTHETEXTINEED'. Given the likelihood that the
filenames will be different lengths you'll need to take a more reliable
approach such as using VBA would offer. This will also address your
'value' issue as it can put the resulting extracted text into a cell's
Value property as preferred...

Sub Extract_ImageFilename()
Dim vData, rng
For Each rng In Selection
vData = Split(rng, "/"): rng.Offset(0, 1) = vData(3)
Next 'rng
End Sub

...where the string is dumped into a zero-based array and the text to
extract (4th element) is put in the next column.

Since you say there are thousands to process, this will take some time
reading/writing one cell at a time. I'd consider dumping the entire
list into an array, build an output array in memory, then dump that
into the next column...

Sub Extract_ImageFilename2()
Dim vDataIn, vDataOut(), n&
vDataIn = Selection: ReDim vDataOut(1 To UBound(vDataIn))
For n = 1 To UBound(vDataIn)
vDataOut(n) = Application.Index(Split(vDataIn(n, 1), "/"), 4)
Next 'rng
Selection.Offset(, 1).Value = vDataOut
End Sub

...where each cell's content is put into a one-based output array and
the text to extract (4th element) is put into its respective position
in the output array.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Thu, 18 Jul 2013 19:51:53 +0100, Larry Walsh wrote:

Hi guys,

We've got a complicated process to post images of our thousands of parts
into our database system.

Currently I have to get the file address of the image, such as:
docs.google.com/file/d/THISISTHETEXTINEED/edit?usp=sharing

The 'THISISTHETEXTINEED' section is, of course,the chunk I need to
extract.

If the portions before and after the extract_text are always the same, then you can use:

=MID(A1,24,LEN(A1)-40)

If those portions might vary, then a formula becomes more complicated:

=MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),-1+LEN(A1)-
LEN(SUBSTITUTE(A1,"/","")))),-1+FIND(CHAR(1),SUBSTITUTE(A1,"/",
CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))-FIND(CHAR(1),
SUBSTITUTE(A1,"/",CHAR(1),-1+LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))

is one way.