I need to cut characters off of a list of text cells...

[Larry Walsh]

Hi guys

We've got a complicated process to post images of our thousands of part
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=sharin

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

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

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

Additionally, I need to be able to copy the output from excel into ou
database. The problem is that when the functions output data, it show
the data, but if you copy the cell, you only copy the function, not th
output value. I can copy the cell containing the function, and past
right next to it with 'paste special - value. This will then show jus
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 tex
automatically.

In other words, if you have a cell that adds the values of two cell
(=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 th
function.
Or, more specifically, I'd like for the results to paste special-valu
into an adjacent cell automatically, without me having to copy and past
it each and every time. Is there a way to do this

Thanks guys

 

[GS]

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

 

[Ron Rosenfeld]

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.